Question Access 2007 date query

[kerberos]

Hi,
I'm creating a winform application that will query a database for all records processed today.
on that row I have dates as 20101229
I need to Count how many rows have the same " date" as today and yesterday.

My problem is how to use the already in place dataset and not create a connectionstring, adapter and query on the form?

Using the query builder on my dataset I'm not able to generate the query because of the date issue

all values of yesterday
SELECT *
FROM BOOKINGS WHERE ( PROCESS_DATE = "#" & format(PROCESS_DATE,"mm/dd/yyyy") & "#" ) = (Date() - “ 1”)

all values of today
SELECT *
FROM BOOKINGS WHERE ( PROCESS_DATE = "#" & format(PROCESS_DATE,"mm/dd/yyyy") & "#" ) = Date()


then I would need a Count how many with the same day as today and yesterday


I'm new at winforms and vb.net, any help will be appreciated,

Thanks,

Kerberos

 

[jmcilhinney]

What EXACTLY is the data type of your column? Format is irrelevant because that just controls how data is displayed in the Access application, not how it's stored in the data file. If your data type is Date/Time then you have no issue. If your data type is Text then that is very, very bad and you need to change it immediately.

 

[kerberos]

Thanks for your reply,

This database is already in place for another application and the datatype for this column is text ('20101229')

all I want is how can I query by date like that.

I cannot change the datatype on this table, my application will simply read from the table and produce reports.

thanks again,

 

[cjard]

Parameterized SQL:
SELECT * FROM BOOKINGS WHERE PROCESS_DATE = ?

In code:
dbCmmand.Parameters.AddWithValue("p1", DateTime.Now.ToString("yyyyMMdd"))

 

[kerberos]

Thank you all,

this is how I managed to use the date on the database by using DateSerial like so:

WHERE DateSerial(Left([Process_Date],4),Right(Left([Process_Date],6),2),Right(Left([Process_Date],8),2))=date();

thanks for all the help.