Date Range Calculation 1

[supermaestro]

Hi. I've looked through the forums and tried a variety of solutions. Basically I have a form which asks the user for an account type, transaction type, a date from and a date to. I am trying to run the query and if there are results then a report is opened else a message is displayed. The problem is that the message is getting displayed everytime even though I know that data exists for the criterias that I am entering. It seems that the query is not calculating the dates properly between the 'from and 'to' dates entered. I have tried using BETWEEN AND along with >= <= but to no avail. The date format is a short date i.e. user enters 01/05/2003 for the 1st May 2003. The [Transaction Date] is the field in the table which I am using to see if is between the user entered dates

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open &quot;SELECT * FROM contacts, transactions WHERE contacts.[Contact ID] = transactions.[Contact ID] And (contacts.[Account Type] = '&quot; & [Forms]![Report Form Options]![Account Type] & &quot;') And (transactions.[Last Transaction] = '&quot; & [Forms]![Report Form Options]![Transaction Type] & &quot;') And (transactions.[Transaction Date] BETWEEN #&quot; & Format([Forms]![Report Form Options]![Transaction Date From], &quot;dd/mm/yyyy&quot & &quot;# AND #&quot; & Format([Forms]![Report Form Options]![Transaction Date To], &quot;dd/mm/yyyy&quot & &quot;#);&quot;
If rst.EOF And rst.BOF Then
MsgBox &quot;There Were No Actions Between Those Dates.&quot;, vbOKOnly
Else
DoCmd.OpenReport &quot;Reports&quot;, acViewPreview
DoCmd.Close acForm, &quot;Report Form Options&quot;
End If
Exit Sub

 

[DanChard]

Virtually all unexpected date problems are due to the difference in US and British date formats (ie, 2nd July 1980 [my birthday] would be 02/07/1980 in the British format but 07/02/1980 in the US format).

Therefore, you putting a date range of between 01/05/2003 and 11/06/2003 would probably evaluate to anything between 5th January 2003 and 6th November 2003! Putting 06/01/2003 or 05/01/2003 as the to date would evaluate to either the 1st of June 2003 or the 1st May 2003. The end result of this is a date range of 5th January 2003 to either 1st June 2003 or 1st May 2003.

Try changing the occurences of dd/mm/yyyy in your query to mm/dd/yyyy and see what happens.

Hope this is of some use...

 

[CosmoKramer]

That's right, check out the MS Knowledge base article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;149095

 

[supermaestro]

Cheers Lads. That sorted it