Query Problem

[Bre-x]

Hi,
I have a table that has a date field = MM/DD/YYYY = 12/31/2004

I have this function that helps me to query my table:

Function CR(FY, FM, TY, TM) As Integer
Dim Q As String
Q = "SELECT Y, M, Dates, SO, Inv, CustID, Cust, CustOrder, JobNum, Desc INTO
YearInv FROM X_Inv WHERE Dates>=#" & FM & "/01/" & FY & "# And Dates<=#" &
TM & "/31/" & TY & "#;"
DoCmd.SetWarnings False
DoCmd.RunSQL Q
DoCmd.SetWarnings True
CR = DLookup("[count]", "SysCountYear")
End Function

If I am query a month that ends on 31 works fine but not for April or
February that ends on 28 or 29

Any idea how can i fix it?

Thanks

Bre-x

 

[Ken Snell]

Change the WHERE clause to this:

WHERE Dates>=#" & FM & "/01/" & FY & "# And Dates<=" &
DateSerial(CInt([TY], CInt([TM]) + 1, 0),

 

[Douglas J. Steele]

Or

WHERE Dates BETWEEN =" & DateSerial(CInt(FY), CInt(FM), 1) & " And " &
DateSerial(CInt(TY), CInt(TM) + 1, 0)

Does Dates also contain time (eg: are you using the Now() function to
populate it)? If so, neither my approach nor Ken's will return data actually
entered on the last day of the month. To get around that, you'll need to use
the first day of the next month instead, which would be DateSerial(CInt(TY),
CInt(TM) + 1, 1)

(Ken: Since TY and TM are parameters to the function, I don't believe it's
correct to enclose them in square brackets as you did.)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Change the WHERE clause to this:

WHERE Dates>=#" & FM & "/01/" & FY & "# And Dates<=" &
DateSerial(CInt([TY], CInt([TM]) + 1, 0),

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a table that has a date field = MM/DD/YYYY = 12/31/2004

I have this function that helps me to query my table:

Function CR(FY, FM, TY, TM) As Integer
Dim Q As String
Q = "SELECT Y, M, Dates, SO, Inv, CustID, Cust, CustOrder, JobNum, Desc INTO
YearInv FROM X_Inv WHERE Dates>=#" & FM & "/01/" & FY & "# And Dates<=#" &
TM & "/31/" & TY & "#;"
DoCmd.SetWarnings False
DoCmd.RunSQL Q
DoCmd.SetWarnings True
CR = DLookup("[count]", "SysCountYear")
End Function

If I am query a month that ends on 31 works fine but not for April or
February that ends on 28 or 29

Any idea how can i fix it?

Thanks

Bre-x

 

[Ken Snell]

Doug -

You're right about not enclosing the arguments in [ ] .. I overlooked that
they were argument variables, and not field names. Thanks!

--

Ken Snell
<MS ACCESS MVP>

Or

WHERE Dates BETWEEN =" & DateSerial(CInt(FY), CInt(FM), 1) & " And " &
DateSerial(CInt(TY), CInt(TM) + 1, 0)

Does Dates also contain time (eg: are you using the Now() function to
populate it)? If so, neither my approach nor Ken's will return data actually
entered on the last day of the month. To get around that, you'll need to use
the first day of the next month instead, which would be DateSerial(CInt(TY),
CInt(TM) + 1, 1)

(Ken: Since TY and TM are parameters to the function, I don't believe it's
correct to enclose them in square brackets as you did.)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Change the WHERE clause to this:

WHERE Dates>=#" & FM & "/01/" & FY & "# And Dates<=" &
DateSerial(CInt([TY], CInt([TM]) + 1, 0),

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a table that has a date field = MM/DD/YYYY = 12/31/2004

I have this function that helps me to query my table:

Function CR(FY, FM, TY, TM) As Integer
Dim Q As String
Q = "SELECT Y, M, Dates, SO, Inv, CustID, Cust, CustOrder, JobNum,

Desc
INTO

YearInv FROM X_Inv WHERE Dates>=#" & FM & "/01/" & FY & "# And

Dates<=#"

&

TM & "/31/" & TY & "#;"
DoCmd.SetWarnings False
DoCmd.RunSQL Q
DoCmd.SetWarnings True
CR = DLookup("[count]", "SysCountYear")
End Function

If I am query a month that ends on 31 works fine but not for April or
February that ends on 28 or 29

Any idea how can i fix it?

Thanks

Bre-x