Query Criteria Question

[jcaptchaos2]

I am trying to get the query to pull the selected data for a date entered [Enter Date M/DD/YYYY] the date looks like "4/27/2011" but is really " 4/27/2011 2:43:39 PM" I can't seem to get a wild card to work. Anybody have a suggestion?

 

[xenou]

Your query criteria can be:

WHERE Table1.Field1 >= [Enter Date m/d/yyyy] AND Table1.Field1 < ([Enter Date m/d/yyyy]+1)

OR

WHERE CLng(Table1.Field1) = CLng([Enter Date m/d/yyyy])

I'm sure there's other ways to do this as well. By habit I usually use the first expression, which includes all times from beginning of the day to the end of the day. The second approach, by contrast, strips the time off so that you only compare the date parts of the values.

I find it helpful to explicitly define the parameters as Dates using the parameters menu on the Query tab (somewhere). Or as the first line of the SQL:

PARAMETERS [Enter Date m/d/yyyy] Datetime;
SELECT .... FROM ... WHERE;

 

[jcaptchaos2]

Thanks Xenou

 

[jcaptchaos2]

Not sure what you mean as I am in query and the field is "Date" and under that the table is "Shop Floor Data" and under that "Group BY" and for criteria I put in >[Enter Date M/DD/YYYY ] and that is kinda working but only because I have no future dates in the table

 

[xenou]

Sometimes you need to change "Group By" to "Where". Also, dates are numbers, and times fractions of a number. So beware. With times, you have to get all dates that are greater than X.000000 and less than X.999999. Or in time values terms, greater than X:00:00:00 and less than X:23:59:59.

 

[jcaptchaos2]

Ok sorry still having trouble, I am using

CLng([Shop Floor Data].[Date])=CLng([Enter Date m/d/yyyy])

and am getting an error "This expression is typed incorrectly or it's to complex to be evaluated.

 

[jcaptchaos2]

This works but only if I amd looking at today, if I type in yesterday I get today and yesterday. Is there a way to modify this

>[Enter Date M/DD/YYYY]

I tried something like this but get an error

>[Enter Date M/DD/YYYY] and <"Date"+1

thanks

 

[boblarson]

This works but only if I amd looking at today, if I type in yesterday I get today and yesterday. Is there a way to modify this

>[Enter Date M/DD/YYYY]

I tried something like this but get an error

>[Enter Date M/DD/YYYY] and <"Date"+1

thanks

The problem is the time element. You have to realize that if you enter the date only then #00:00:00# is appended to the end of it and so it becomes midnight of the day. So for the beginning date that isn't a problem but for the ending date it is because you will get only from the beginning date until midnight of the day ending. So, you can append your own to it (or you can use the date + 1 like this:

Between [Enter Date M/DD/YYYY] And Date() + 1

 

[jcaptchaos2]

Ok thanks Bob, I don't think the Date() part is correct though as if I'm not mistaken that would refer to todays date, I need it so it looks at the date that is entered in the [Enter Date M/DD/YYYY] part of the formula. The field name is "date' for what they are typing in.

 

[boblarson]

Ok thanks Bob, I don't think the Date() part is correct though as if I'm not mistaken that would refer to todays date, I need it so it looks at the date that is entered in the [Enter Date M/DD/YYYY] part of the formula. The field name is "date' for what they are typing in.

Oh, if you only want a single date then you should be able to use

Between [Enter Date M/DD/YYYY] And [Enter Date M/DD/YYYY] + 1