locked
AccessDataSource not seeing some data in imported query RRS feed

  • Question

  • User-219676347 posted

    I'm using Visual Web Developer 2010 to create a webpage for employees to view information in MS Access database.  The problem is that the AccessDataSource not seeing some of the data imported from MS Access query.  The data is definitely there in MS Access query, but when I configure the AccessDataSource and test the string, all data is there except a calculated field for the length of stay.  I have a feeling that this is normal, and I will have to rewrite the query in VWD.  I hope not, as I don't know VWD that well yet.  I hope that there is a little trick that can make that data show up.  If not can you point me in the direction to learn how to build a query that will not only pull the data, but also calculate the difference between admit date and "today" to get the length of stay.<o:p></o:p>

    Thursday, April 14, 2011 4:57 PM

Answers

  • User-1199946673 posted

    I've tried your suggestion and I'm still getting that error I spoke of.  "Undefined function GETDATE in expression"  Any thoughts?

    I already said that the GETDATE() function is a SQL Server function, you shoudl use Date()

    Try:

    

    SELECT Clients.AdmitDate, Clients.Last, Clients.First, Clients.CloseDate, Clients.Service, Clients.DOB, Date() AS Today, DateDiff('d',[AdmitDate],Date())+1 AS LOS, DateDiff('d',[AdmitDate],Date()) AS LOS2 
    FROM Clients 
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 16, 2011 7:33 AM

All replies

  • User-1199946673 posted

    The data is definitely there in MS Access query, but when I configure the AccessDataSource and test the string, all data is there except a calculated field for the length of stay

    Show us the query?

    Friday, April 15, 2011 2:17 AM
  • User-219676347 posted

    OK Jerry McGuire here's the query!  (Little levity)  I really appreciate your wanting to look at it.  I really hope you can help.  I've been playing with VWD query builder, and I can't find a way to do this.  Neither [LOS] nor [LOS2] data shows.  I had [LOS2] hidden but made it visible for this post.  All other data is present in VWD, especially [AdmitDate] and [Today] from which [LOS] is calculated.

     

    SELECT Clients.AdmitDate, Clients.Last, Clients.First, Clients.CloseDate, Clients.Service, Clients.DOB, Date() AS Today, [LOS2]+1 AS LOS, DateDiff("d",[AdmitDate],[Today]) AS LOS2
    FROM Clients
    WHERE (((Clients.CloseDate) Is Null));
    

     

    Friday, April 15, 2011 12:45 PM
  • User797213118 posted

    have you tried this

    DateDiff("d",[AdmitDate],GETDATE()) AS LOS2

    hope this helps

    Friday, April 15, 2011 8:02 PM
  • User-219676347 posted

    I did find out about the GETDATE() and tried it several ways, and got error messages on them all.  I wanted to make sure I tried it the way you mentioned, so I tried to install VWD 2010 on my vista laptop.  Due to non compatibility issues, I had to install VWD 2008.  Guess what?  All of the data showed.  No problems whatsoever.  Must be a glitch in 2010.

    Saturday, April 16, 2011 2:28 AM
  • User-1199946673 posted

    have you tried this

     

    DateDiff("d",[AdmitDate],GETDATE()) AS LOS2

     

    hope this helps

    Off Course not!!! GETDATE() is an SQL SERVER Function, it has nothing to do with Access...

    PsycoDaD

    You must realize that the VWD query designer doesn't work pretty well with Access. So many times, you need to write your query's manually. Or write themin Access, and then copy them to VWD. When doing that, Realise that not all functions that are available in Access can be used when connecting to an MDB file in VWD, Because then you're actually dealing with a Jet Database Engine. An axample of a functiona thta is missing in Jet is the Replace function. And also, when working with the Like operator, the wildcard character is %, while in Access, the wildcard character is *

    Saturday, April 16, 2011 2:54 AM
  • User-219676347 posted

    Well after playing around with table adapters, I lost the LOS field again.  I've tried your suggestion and I'm still getting that error I spoke of.  "Undefined function GETDATE in expression"  Any thoughts?

     

    Saturday, April 16, 2011 6:00 AM
  • User-1199946673 posted

    I've tried your suggestion and I'm still getting that error I spoke of.  "Undefined function GETDATE in expression"  Any thoughts?

    I already said that the GETDATE() function is a SQL Server function, you shoudl use Date()

    Try:

    

    SELECT Clients.AdmitDate, Clients.Last, Clients.First, Clients.CloseDate, Clients.Service, Clients.DOB, Date() AS Today, DateDiff('d',[AdmitDate],Date())+1 AS LOS, DateDiff('d',[AdmitDate],Date()) AS LOS2 
    FROM Clients 
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 16, 2011 7:33 AM
  • User-219676347 posted

    Sorry missed that.  It's working now, thanks.Smile

    Sunday, April 17, 2011 12:39 AM