Avatar of vibale
vibale
 asked on

Date Query Problem

Hi,

Im running a query thorugh code but its not working. I think I know why its not working but dont know how to get it to work

Im querying date fields and its not working....however if I make an actual query, and enter the dates in the condition fields in the following format

dd/mm/yyyy

the query works

When I look at the sql code of the query, the date fields are changed to

mm/dd/yyyy

The format I have the dates in is dd/mm/yyyy...........how can I get my manual query to work?

Also, I have checked the table design and it is set to short date (dd/mm/yyyy)????
Microsoft Access

Avatar of undefined
Last Comment
vibale

8/22/2022 - Mon
rockiroads

u could try formatting dates on both sides to be yyyymmdd
e.g.

where format(somedatefield,"yyyymmdd") = format(otherdatefield,"yyyymmdd")
Mike Eghtebas

if date field is StartDate, then rewrite it as:

Start_Date: Format(StartDate,"yyyymmdd")

And enter the dates as 20061015

GRayL

If there is any ambiguity in the date criteria even though entered as dd/mm/yyyy, Access will change it to its default mm/dd/yyyy.  06/07/2008 is interpreted by Access as 2008/06/07 even though the system short date setting is dd/mm/yyyy.  Leave all those problems behind you by using a system short date format that is never ambiguous -

yyyy/mm/dd or dd-mmm-yyyy  -  use what ever date separator suits you.  
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Moother

fact: access uses dates in calculations in USA format only..., that is to say... mm/dd/yy...

well..., that's not quite true... access works with dates in serial format i.e. the number of days since 01/01/1900 = today....  so access sees 31/12/1900 as 365 - an integer (1900 was NOT a leap year)

so if you use dates in calculation either:
1) convert all to USA format
2) convert the date to an integer with a format(date(),"00000") and then back with another format()
3) use the Dateserial(y,m,d) function

it sucks..., but it's a fact.
boxcar7

I think this is a function of your Language/Locality settings, and I'm not sure that you can change this without changing it across Windows.  If you go into your Control Panel and select "Regional and Language Options" (might be slightly different wording depending upon the OS), you can alter the default short date format.  This will affect the behavior of the operating system as well as other applications (all the Office applications at the very least).  Note, this change will only affect how dates are formatted on the local machine.  Someone using the database on a machine with different locality settings will look different.

When building your queries with code, I would recommend using date serial values rather than formatted dates.  (Look at the dateserial function).
vibale

ASKER
The problem is is that im using a popup calendar, and the 2 dates (startdate and endate) are then stored in comboboxes

I then need to query a table based on those 2 combo's.......how would I achieve this?

The 2 combos are called cbostartdate and cboenddate

Thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GRayL

Moother:  Access sees #1900-12-31# as 366 not 365 - which is why Format(0,"yyyy-mm-dd") is 1899-12-30.  Lotus Notes designed their calaendar (before Microsoft) and forgot that 1900 was not a leap year.  So in order to be compatible, Bill made the MS calendar day zero = 1899-12-30.  The only thing worse than no information is misinformation;-)
GRayL

Select myTable.* from myTable where myTable.myDate Between Forms.myFormName.cbostartdate and Forms.myFormName.cboendDate;

BTW what is your short date system setting, what dates are in the combobox, and does the format of the date fields in the combobox match your short date system setting.  If this is all right, then the above query should work - provided there is no ambiguity - as I said in a previous post,
vibale

ASKER
Right, I have a table which has 2 date fields, both set to the following format:


dd/mm/yyyy


In this table, the dates are entered as 31/08/2006, 07/10/2006 etc etc

The dates in the combo boxes are set as 31/08/2006 etc etc

Still cant get it to work?????

Here is my sql statement

SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between " & Format(cboStartDate, "dd/mm/yyyy") & " And " & Format(cboEndDate, "dd/mm/yyyy") & ";"

Have also tried

SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between " & cboStartDate  & " And " & cboEndDate & "#;"

And

SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between " & Format(cboStartDate, "ddmmyyyyy") & " And " & Format(cboEndDate, "ddmmyyyy") & ";"

And

SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between " & Format(cboStartDate, "yyyymmdd") & " And " & Format(cboEndDate, "yyyymmdd") & ";"

Thanks

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
GRayL

But don't you see - 07/10/2006 - is ambiguous.  In all cases, Access will say that is 10 Jul 2006, even though your format is 'dd/mm/yyyy' - even though you meant 7 Oct 2006.  
vibale

ASKER
so how do I get it to work, ive tried everything suggested?
boxcar7

It's odd behavior.  Rather than trying to format the date in the SQL, use the date serial.  For example, replace "Format(cboStartDate, "dd/mm/yyyy")" from your SQL statement above with Str(dateserial(Year(cboStartDate),Month(cboStartDate),Day(cboStartDate))) to eliminate formatting problems.  Does that provide the proper results?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
vibale

ASKER
Do I also have to do that on the value im querying?

I.E

Select * from tbljobs where Str(dateserial(Year(tbljobs.fldorderdate),Month(tbljobs.fldorderdate),Day(tbljobs.fldorderdate))) = ............


??

Thanks
boxcar7

The formatting in the table should not matter.  Date/times are stored in access as numbers, so they should be able to compare a date field to a serially formatted number.

Obviously I am assuming that the field in question is a date field and not a text field.  If so, the formatting in field's properties only affect the way the data is presented in datasheet view, not the way it is stored in the database.
boxcar7

...if that wasn't clear -- no, you should be able to say Select * from tbljobs where tbljobs.fldorderdate = ...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
vibale

ASKER
ok cool ill give it a go and let you know how i get on

thanks
ASKER CERTIFIED SOLUTION
boxcar7

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
vibale

ASKER
have tried both and neither worked
vibale

ASKER
Its working!!!

Here is the statement that worked:

SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between #" & Format(cboStartDate, "mm/dd/yyyy") & "# And #" & Format(cboEndDate, "mm/dd/yyyy") & "#;"
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rockiroads

If that worked for u then Im surprised the YYYYMMDD format flags didnt work, Ive always had 100% success with that
vibale

ASKER
Nope that one defintely didnt

Thanks again matey
rockiroads

No probs, very interested in your problem now though as its the 1st time Ive never seen it work.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
vibale

ASKER
Its probably down to someting ive done, its all a bit of a mess at the moment!!