[Urgent Help] select statement with Date Field

[lok1234]

Dear all,

I am really confused with Access Date field handling.

I have the following query (assume TableA has a field "wdays" which stores all the dates in Year 2009):

select top 1 format(wdays,'dd/mm/yyyy') as wdays1
from TableA
where year = 2009
and WDays >= format('10/04/2009', 'dd/mm/yyyy')
order by wdays asc

It will select 11/04/2009 which is fine.

But in case i use this SQL:

select top 1 format(wdays,'dd/mm/yyyy') as wdays1
from TableA
where year = 2009
and format(WDays,'dd/mm/yyyy') >= format('10/04/2009', 'dd/mm/yyyy')
order by wdays asc

the select behaviour will be totally different. It will select 12/01/2009! (I don't know why it is 12/01/2009)

I am very confused with the function: "FORMAT" now..

I have a VB program which has a lot of queries.
Some of the queries sometimes use:
format(mydate,'dd/mm/yyyy') = format(v_inputdate,'dd/mm/yyyy')
Or Some of them just simply use:
mydate=v_inputdate
Or Some use:
mydate = format(v_inputdate,'dd/mm/yyyy')
Or some use
format(mydate,'dd/mm/yyyy') = v_inputdate

Would you mind please teach me what is the best practise for writing select, update, delete, insert sql statements when date or datetime fields are included in the conditions?

Many Many thanks.. I am super urgent to know the answer.

 

[DCrake]

select top 1 format(wdays,'dd/mm/yyyy') as wdays1
from TableA
where year = 2009
and WDays >= format('10/04/2009', 'dd/mm/yyyy')
order by wdays asc

1. Have you a field in your table called year?
...If so, this is not needed as you can extract the year from the date using Year(wdays)

2. You need to encompass dates with #'s
... Wdays >= Format(#01/04/2009#,"dd/mm/yyyy")

3. If you are only selecting the top 1 why are you performing a sort as you are only returning 1 record.

 

[lok1234]

1. Have you a field in your table called year?
...If so, this is not needed as you can extract the year from the date using Year(wdays)

2. You need to encompass dates with #'s
... Wdays >= Format(#01/04/2009#,"dd/mm/yyyy")

3. If you are only selecting the top 1 why are you performing a sort as you are only returning 1 record.

thanks for your prompt reply.
1. Yes, I have a field called "Year"
2. so, do I need need to write in this way?
format(Wdays,'dd/mm/yyyy') >= Format(#01/04/2009#,"dd/mm/yyyy")
for the comparison of left hand side and right hand side in the query?
3. i use "order by" because I want to make sure the result will return the nearest day.

Further question:

So, do I need to always write in this way:
any date field = Format(#01/04/2009#,"dd/mm/yyyy")

in case i write in these ways, is there any potential issue?
Wdays = #01/04/2009#
or
format(Wdays ,'dd/mm/yyyy') = Format('01/04/2009',"dd/mm/yyyy")



Many thanks!

 

[lok1234]

ohooh.. I have tried:

select top 1 format(wdays,'dd/mm/yyyy') as wdays2
from TableA
where year = 2009
and WDays >= format(#10/04/2009#, 'dd/mm/yyyy')
order by wdays asc

The result is horrible, it returns 05/10/2009

why the behaviour is unpredictable and not consistent?

Many thanks in advance

 

[DCrake]

Are your dates not being stored as dd/mm/yyyy?

Go to the design of your table and in the format property enter

dd/mm/yyyy
in the input mask enter

99/99/0000;0;_

now when you run your query it should always be in dd/mm/yyyy format so you do not need to include the format() mask.

Year is an Access reserved word so it should not be used as a field name. And like I said you can extract the Year form any date using the Year() function.

 

[lok1234]

Are your dates not being stored as dd/mm/yyyy?

Go to the design of your table and in the format property enter

dd/mm/yyyy
in the input mask enter

99/99/0000;0;_

now when you run your query it should always be in dd/mm/yyyy format so you do not need to include the format() mask.

Year is an Access reserved word so it should not be used as a field name. And like I said you can extract the Year form any date using the Year() function.

Many thanks for your kindly help.

I will delete the "Year" field, and currently, i am more concern about the FORMAT function because I have to re-write over 500 queries in my VB program in case the "FORMAT" is used in-correctly.
Let us discuss on "FORMAT" first.

I want to know, why if I use this query, the result is totally different than expect (p.s. I have already changed the Wdays field with input mask as dd/mm/yyyy)

select top 1 format(wdays,'dd/mm/yyyy') as wdays2
from main_fund_wdays
where format(WDays,'dd/mm/yyyy') >= format('10/04/2009','dd/mm/yyyy')
order by wdays asc

This query will return 12/01/2009

 

[DCrake]

where format(WDays,'dd/mm/yyyy') >= format('10/04/2009','dd/mm/yyyy')

What you are doing is converting a Date field into a string type and becuse "12" is greater than "10" then the answer is correct.

What happens if you leave the formatting out?

 

[lok1234]

where format(WDays,'dd/mm/yyyy') >= format('10/04/2009','dd/mm/yyyy')

What you are doing is converting a Date field into a string type and becuse "12" is greater than "10" then the answer is correct.

What happens if you leave the formatting out?

If using this:
where WDays >= format('10/04/2009','dd/mm/yyyy')

The result will be FINE

 

[lok1234]

So, in my VB programs, there are about 200 queries writing likes that:

where format(ABC,'dd/mm/yyyy') = format(DEF,'dd/mm/yyyy')

do you think i need to change it back to:
where ABC = format(DEF,'dd/mm/yyyy')

?

Many many thanks

 

[DCrake]

Can you post a small snipped of data form me to look at and a sample query of what you attempting to acheive?

David

 

[lok1234]

Hi DCrake,

Thank you a lot for your kindly help. Since I am currently in company, and according to my company's security and privacy policies, I cannot upload anything in Internet, so, i just can do it tonight when I back home.

Actually, I have a VB program which consists a lot of queries to retrieve, update, delete and insert data to Access Tables. And most of the queries are having Date fields in the condition. And since we have 3 developers in this project, and we are writing in different style, therefore, the queries for date time handling are different.

What i want to do it right now is to unify the queries that with date fields in the conditions. And it is really a harsh task.

anyway, thank you for your help. And I will try to upload the queries and sample data to u tonight.

 

[WIS]

format(mydate,'dd/mm/yyyy')

Use Format(mydate, 'dd/mmm/yyyy')