Using "between" and "and" for dates

[harrisharris]

I am going through a simiple tutorial where the example query is:
SELECT*
FROM Invoice
WHERE inv_date between #10/12/99# and #14/1/00#


The results I get do not display invoices between these dates.

I am using ACCESS 2002, the book I am learing from was published in 2000. Do I have a version problem or what?

Help on this would be much appreciated.

 

[JimHorton]

harris,
First, if your date format is Standard, 14/1/00 is not a valid date. Also, be aware that 'between' is a misnomer, 'between' in SQL means 'between AND including'
--Jim

 

[harrisharris]

Thanks Jim,

My date format was the problem, however, I have chosen "General" as the date format on the inv_date field and now find that in the Query i have to type the date the USA way (month/day/year). The language and regional settings in my control panel are UK. Any thoughts?

 

[JimHorton]

harris,
What does the query return--nothing, or some incorrect set?
--Jim

 

[r937]

code your dates as ISO Standard, access has never had problems with this format

between #1999-12-10# and #2000-01-14#


rudy

 

[flyboytim]

from Access Help:

You must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.

 

[harrisharris]

Jim,
Thanks for your help, you pinted me in the right direction, I was using the incorrect date format.


Rudy,
Thankyou too, the ISO format works perfectly. I haven't got as far as designing the form for this query yet, but when I do, will the form user have to use ISO format for date entry?

Tim,
Thanks for your contribution as well.

 

[r937]

not necessarily, as long as the user uses a format which leaves no doubt about whether it's April 3 or March 4

unfortunately this throws 03/04/03 into a cocked hat

so yeah, give the user instructions to enter dates in yyyy-mm-dd format

rudy

 

[SgtJarrow]

harrisharris,

If your users are ALL comfortable with one date format, you can merely change the format to the ISO standard after they type it in the format the are comfortable with....

In the textbox they enter the date into, use an after update event to merely format the date as you need it stored...

You could go one step further and have a hidden text box which is the bound field to your table and contains the format for the ISO standard. You use a visible unbound box that has the date format comfortable to your users. When the users enter a date in the visible box, the afterupdate event formats it to ISO standard and puts it into the hidden date field, which is what is stored at the table level. If you then need that date agin, say for a report or whatever, you just change the display of the table field to whatever the users need.... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[harrisharris]

Rudy,
Thanks for that.

Robert,
I'm interested in trying out the "In the textbox they enter the date into, use an after update event to merely format the date as you need it stored..."
I went into the 'Update event' property for inv_date and was given the option of an expression builder or entering some VB code. This is now a bit beyond me, could you give me a pointer on this. My user would enter the date as dd/mm/yy and I would need to convert that to ISO yyy/mm/dd

Thanks in anticipation

Laurence Hill

 

[SgtJarrow]

Sure....find the afterupdate event and unsing the combobox select Event Procedure. This will force the event to evaluate code for that event. Click the ellipse (3 dots) to the right of this event and you shoudl be brought to the code window, with your focus in the event you just selected. In this window, put the following, making sure it is between the cmdButtonName_Click and End Sub:

Me![inv_date] = Format(Me![inv_date], "yyyy/mm/dd&quot

Save this and test it out....When the user types 05/26/75, and hits enter or leaves the field, the after update event fires. The after update is now set to find the field you just entered and change the date format from 05/26/75 to 1975/05/26. This new value is then placed in the box you just typed.

Let me know how this works for you....Still confused? - send me an email to my work and I will forward you an example. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[SgtJarrow]

Oops...ignore that part about the cmbButtonName_Click...

It should read:

inv_date_AfterUpdate Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com