So all that code does is change the SQL string associated with your query
named "all_areas by_op_occur".
Are you saying that once the SQL has changed and you run the query, it
shows
everything? Once you've run that VBA, is what's actually in the SQL of
the
query correct?
You might try renaming your Date field: Date is a reserved word, and
should
never be used for your own purposes. If you cannot (or will not) change
the
field name, at least put square brackets around it:
Set MyDB = CurrentDb()
Set qdf = MyDB.QueryDefs("all_areas by_op_occur")
strSQL = "SELECT [all_areas_by_op].OP, [all_areas_by_op].[date] ,
[all_areas_by_op].shift, [all_areas_by_op].sumofoccur,
[all_areas_by_op].team, [all_areas_by_op].reason,
[all_areas_by_op].sumofDowntime " & _
"FROM [all_areas_by_op] " & _
"Where [all_areas_by_op].Team='" & Me.TXTHOLD.Value & "'"
& _
"AND [all_areas_by_op].[Date] Between " &
SQLDate(Me.txtStartDate1.Value) & " and " &
SQLDate(Me.txtEndDate1.Value) & _
"AND [all_areas_by_op].OP Not In ('No Problems')"
qdf.SQL = strSQL
Set qdf = Nothing
Set MyDB = Nothing
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
kevcar40 said:
OK Douglas
Here is the SQL Statement i am using
Set MyDB = CurrentDb()
Set qdf = MyDB.QueryDefs("all_areas by_op_occur")
strSQL = "SELECT [all_areas_by_op].OP, [all_areas_by_op].date ,
[all_areas_by_op].shift, [all_areas_by_op].sumofoccur,
[all_areas_by_op].team, [all_areas_by_op].reason,
[all_areas_by_op].sumofDowntime " & _
"FROM [all_areas_by_op] " & _
"Where [all_areas_by_op].Team='" & Me.TXTHOLD.Value & "'"
& _
"AND [all_areas_by_op].Date Between " &
SQLDate(Me.txtStartDate1.Value) & " and " &
SQLDate(Me.txtEndDate1.Value) & _
"AND [all_areas_by_op].OP Not In ('No Problems')"
qdf.SQL = strSQL
Set qdf = Nothing
Set MyDB = Nothing
hope this helps
thanks
Douglas J. Steele wrote:
You'll need to show more of your code, then, so that we can see how
you're
using that SQL.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Thank you for your reply
the query is returning every record
Douglas J. Steele wrote:
Not sure whether this is it, but you're missing some blanks in your
SQL
(After Between and before and):
Between " & SQLDate(Me.txtStartDate1.Value) & " and " &
SQLDate(Me.txtEndDate1.Value) & _
By the way, your SQLDate function could be replaced by
Format(DateField,
"\#mm\/dd\/yyyy\#")
When you say "this is not returning the required date range",
what's
missing? If it's data corresponding to txtEndDate1, does your date
field
also include time? (i.e.: was it populated using the Now function,
rather
than the Date function?) If so, try:
Between " & SQLDate(Me.txtStartDate1.Value) & " and " &
SQLDate(DateAdd("d", 1, Me.txtEndDate1.Value)) & _
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Can anyone help please
i am importing a table from microsoft excel which contains a date
field
fomatted dd/mm/yyyy
in access i am using a date function to query between two dates
the function is
Function SQLDate(vDate As Variant) As String
If IsDate(vDate) Then
SQLDate = "#" & Month(vDate) & "/" & Day(vDate) & "/" &
Year(vDate) & "#"
End If
End Function
the call to this function is
Between" & SQLDate(Me.txtStartDate1.Value) & "and " &
SQLDate(Me.txtEndDate1.Value) & _
txtStartDate1 and txtEndDate1 are both text boxes that hold the
date
value from a calender pop up form
the problem i have is this is not returning the required date
range
can anyone please tell me why?
thanks
kevin
