problem with date in the query

[ras81]

hi

i wish to find a date (format=general Date) in a table and for some reason it give me a blank (null) anwer
here is my query:

SELECT Order.InvoiceNum, Order.time, Order.[Total Price], Order.[Cash/Credit], Order.Company, Order.CustomerPhone, Users.WorkerName
FROM Users INNER JOIN [Order] ON Users.WorkerID = Order.DeliverID
WHERE (((Order.time)=([Forms]![OrderHistory]![HDate])));

the date in theorder form is also "general date" format and all i want is to find all the orders in the same date.

can anyone help me plz.
thanks

 

[Brianwarnock]

I notice that it is called order.time which suggests that it has date and time, Hdate on the form probably only has date input and thus defaults to a time of 00:00:00, change the field for the criteria to Format([time], "dd/mm/yyyy").

The use of words like time for field names is not good, it leads to confusion and problems.

Brian

 

[ras81]

no change

thanks Brian
u are correct with the full date+hour issue, but even after i wrote tried to change the format there was no change

SELECT Order.InvoiceNum, Order.time, Order.[Total Price], Order.[Cash/Credit], Order.Company, Order.CustomerPhone, Users.WorkerName
FROM Users INNER JOIN [Order] ON Users.WorkerID = Order.DeliverID
WHERE [B]Format(Order.[time], "dd/mm/yyyy")[/B]=[Forms]![OrderHistory]![HDate];

 

[raskew]

Hi -

WHERE Format(Order.[time], "dd/mm/yyyy")=[Forms]![OrderHistory]![HDate];

Instead, try:

WHERE DateValue(Order.[time]) =[Forms]![OrderHistory]![HDate];

This is assuming that [Forms]![OrderHistory]![HDate] is strictly a date, with no time recorded.

The DateValue() function returns just the date, no time. Take a look at this
MSKB article describing how Access stores Dates/Times: http://support.microsoft.com/kb/q130514/

As an example from the debug (immediate) window:
x = now() 'the current date and time
? x
8/9/2008 7:45:34 AM
? cdbl(x)
39669.3233101852 'this is how Access stores it

y = datevalue(x)
? y
8/9/2008
? cdbl
39669

Note that formatting doesn't get rid of the time portion of a date/time field.
The DateValue() function does.

HTH - Bob

 

[Brianwarnock]

I'm sorry answered in a hurry and didn't explain myself properly, I meant create a new field Expr1:Format(time], "dd/mm/yyyy") to which you apply the criteria, that works, but Bob is correct in his approach and explanations.

Brian

 

[ras81]

thanks brian and bob
i really appreciate your help, both methods worked

thank u and have a great day
roy