Query Problem

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

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
 
Change the WHERE clause to this:

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


Ken Snell said:
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>

Bre-x said:
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
Click to expand...
Click to expand...
 
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>

Douglas J. Steele said:
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)


Ken Snell said:
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>

Bre-x said:
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,
Click to expand...
Desc
INTO
YearInv FROM X_Inv WHERE Dates>=#" & FM & "/01/" & FY & "# And
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top