Could you try a few different dates? see if you get different results for dates that are clearly uk (i.e. the day is 13 or over, like 30/01/2003) to ones that could be confused for US (e.g. 07/04/2004). Only thing is to make sure of is your dates aren't 01/01/2004, 02/02/2004 etc as obviously these are the same either way. Gist is, in certain circumstances VBA ignores all your date format settings and treats dates as US if it can. I may have the wrong end of the stick, but from your example dates this could feasibly be the problem.
If it does work, I would keep your text box as it was (i.e. "Start_Date between #" & Me.StartDate & "# and #" & Me.EndDate & "#"), if the intention is to show the user SQL. This is to fix a VB anomaly, not a SQL one.
In any case, if there are any other people who understand precisely why and when VB desides to ignore your carefully formatted date fields, textboxes and regional settings I'd be interested to hear a definitive answer. I have a folder full of stuff off the web purporting to explain and offer fixes that don't work. My own theories change every time I come up against the problem.
Last edited by pootle flump; 05-07-04 at 16:32.
Reason: Forgot to handle null values
SO let me get this right I tell Access that the text box has the format dd/mm/yyyy and the field in the table also has format dd/mm/yyyy and my regional settings are set to UK ie dd/mm/yyyy.
In VBA swaps my dd and mm to give mm/dd/yyyy. What help is that I have alreadt forced the change 3 times. Is this one for microsoft? I am not going to waste my time and yours by complaining about Microsoft.
Oh, so it's concatenation that does it! Could never figure out why you can compare and test date values assigned to a variable no probs, yet as soon as it's popped into a SQL string it goes haywire. Cheers and much appreciated. Knowing the reason is 90% of the battle.