Dates Not updating subform RecordSource

[MaliciousMike]

Hello,

I have a dropdown menu which has a lisrt of dates (mmm yyyy), and when one is selected it updates 2 boxes with 01/mm/yyyy and 30 or 31/mmm/yyyy

Private Sub cmbDate_AfterUpdate()
    Me.frmData.Form.RecordSource = "SELECT * FROM qry_NatxReg WHERE [Company Name]='" & cmbCompanyName & "' AND [Time of Attempt 1] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#"
    Me.frmData.Form.Requery
End Sub

When i choose June, it works fine and gets all the june dates from [Time Attempt 1]

But when i Choose July, it includes June.

I only have data for June and July as it's still in testing.
I can't wrap my head round it!!

 

[MaliciousMike]

I've already got that.
FirstOfTheMonth = DateSerial(Year([cmbDate]),Month([cmbDate]),1)
LastOfTheMonth = DateSerial(Year([cmbDate]),Month([cmbDate])+1,0)

The dates are all working fine, these fields display 01/07/2006 and 31/07/2006 when i select "Jul 2006", but it's showing June.

See attached screenshots of when i choose July and June.

 

[MaliciousMike]

Sorry, Deadline on monday.

Bump.

 

[Banana]

Did you step through the code? Did you use Debug.Print on all variables before and after update? If it's giving you wrong result, the likely answer is that there is a logical error in your coding.

 

[MaliciousMike]

It's werd though...
Why does it work when i select june (getting rid of July Dates) but not when i select July?

Does it matter that the field i'm trying to filter was automatically filled in with July dates, and that i manually changed it to June?

 

[MaliciousMike]

Ok, i found out more about the problem.

It's selecting all dates below the date chosen aswell.

So selecting July would show June, and selecting June would show May.

Me.frmData.Form.RecordSource = "SELECT * FROM qry_NatxReg WHERE [Company Name]='" & cmbCompanyName & "' AND ([Time of Attempt 1] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#)"

 

[MaliciousMike]

I've tried so many different ways to try and get this working but it just wont work.

SELECT * FROM qry_NatxReg WHERE [Time of Attempt 1] >= #01/07/2006# AND [Time of Attempt 1] <= #31/07/2006# AND [Company Name]='Alex Homes'

SELECT * FROM qry_NatxReg WHERE ([Time of Attempt 1] BETWEEN #01/07/2006# AND #31/07/2006#) AND [Company Name]='Alex Homes'

How in hell, are these queries displaying june?
NOTHING WORKS!!
*frustration!*

 

[neileg]

I agree with Pat. When you use stored querydefs, even if these use raw SQL, Access seems to cope with UK date formats. When you build the SQL in string form, you must use US formats, or, as Pat says, an unambiguous format like dd-mmm-yyyy.

 

[MaliciousMike]

=Format(DateValue(DateSerial(Year([cmbDate]),Month([cmbDate]),1)),"dd/mm/yyyy")
=Format((DateSerial(Year([cmbDate]),Month([cmbDate])+1,0)),"dd/mm/yyyy")

Still seems to be giving me all dates before the month aswell.

Did the format in the SQL string too, and still giving the problem.

Changed the SQL String to "mm/dd/yyyy" and it's working fine...
F@&*ing date formats!!

If microsoft were universal it would be yyyy/mm/dd, but noooooo!!!

Thanks for all the help guys. Appreciate it!