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

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
http://I.Am/DougSteele
(no e-mails, please!)


"kevcar40" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "kevcar40" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >

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
http://I.Am/DougSteele
(no e-mails, please!)


"kevcar40" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "kevcar40" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>

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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "kevcar40" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "kevcar40" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >
> >

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
http://I.Am/DougSteele
(no e-mails, please!)


"kevcar40" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "kevcar40" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >> http://I.Am/DougSteele
>> >> (no e-mails, please!)
>> >>
>> >>
>> >> "kevcar40" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > 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
>> >> >
>> >
>

Douglas
I have change the field name from Date to Mydate
When i run the code iand the SQL statement has change it Does return
every record after running the VBA the SQL is

SELECT [all_areas_by_op].OP, [all_areas_by_op].Mydate,
[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].MyDate Between #12/11/2006# and #12/13/2006#AND
[all_areas_by_op].Team='A1'AND [all_areas_by_op].OP Not In ('No
Problems');


Douglas J. Steele wrote:
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "kevcar40" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "kevcar40" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >> http://I.Am/DougSteele
> >> >> (no e-mails, please!)
> >> >>
> >> >>
> >> >> "kevcar40" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > 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
> >> >> >
> >> >
> >

You seem to be missing some spaces around the ANDs. See whether that makes a
difference.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"kevcar40" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Douglas
> I have change the field name from Date to Mydate
> When i run the code iand the SQL statement has change it Does return
> every record after running the VBA the SQL is
>
> SELECT [all_areas_by_op].OP, [all_areas_by_op].Mydate,
> [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].MyDate Between #12/11/2006# and #12/13/2006#AND
> [all_areas_by_op].Team='A1'AND [all_areas_by_op].OP Not In ('No
> Problems');
>
>
> Douglas J. Steele wrote:
>> 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
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "kevcar40" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >> http://I.Am/DougSteele
>> >> (no e-mails, please!)
>> >>
>> >>
>> >> "kevcar40" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > 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
>> >> >> http://I.Am/DougSteele
>> >> >> (no e-mails, please!)
>> >> >>
>> >> >>
>> >> >> "kevcar40" <(E-Mail Removed)> wrote in message
>> >> >> news:(E-Mail Removed)...
>> >> >> > 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
>> >> >> >
>> >> >
>> >
>

Also, I just reread the thread, and noticed you stated you've imported the
data from Excel. Did Access recognize the field as a date, or is it text in
the table?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:(E-Mail Removed)...
> You seem to be missing some spaces around the ANDs. See whether that makes
> a difference.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "kevcar40" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Douglas
>> I have change the field name from Date to Mydate
>> When i run the code iand the SQL statement has change it Does return
>> every record after running the VBA the SQL is
>>
>> SELECT [all_areas_by_op].OP, [all_areas_by_op].Mydate,
>> [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].MyDate Between #12/11/2006# and #12/13/2006#AND
>> [all_areas_by_op].Team='A1'AND [all_areas_by_op].OP Not In ('No
>> Problems');
>>
>>
>> Douglas J. Steele wrote:
>>> 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
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "kevcar40" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> > 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
>>> >> http://I.Am/DougSteele
>>> >> (no e-mails, please!)
>>> >>
>>> >>
>>> >> "kevcar40" <(E-Mail Removed)> wrote in message
>>> >> news:(E-Mail Removed)...
>>> >> > 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
>>> >> >> http://I.Am/DougSteele
>>> >> >> (no e-mails, please!)
>>> >> >>
>>> >> >>
>>> >> >> "kevcar40" <(E-Mail Removed)> wrote in message
>>> >> >> news:(E-Mail Removed)...
>>> >> >> > 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
>>> >> >> >
>>> >> >
>>> >
>>
>
>

Thanks Douglas
i will give it a go tomorrow
done my head in now

thanks again
kevin
Douglas J. Steele wrote:
> You seem to be missing some spaces around the ANDs. See whether that makes a
> difference.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "kevcar40" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Douglas
> > I have change the field name from Date to Mydate
> > When i run the code iand the SQL statement has change it Does return
> > every record after running the VBA the SQL is
> >
> > SELECT [all_areas_by_op].OP, [all_areas_by_op].Mydate,
> > [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].MyDate Between #12/11/2006# and #12/13/2006#AND
> > [all_areas_by_op].Team='A1'AND [all_areas_by_op].OP Not In ('No
> > Problems');
> >
> >
> > Douglas J. Steele wrote:
> >> 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
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "kevcar40" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >> http://I.Am/DougSteele
> >> >> (no e-mails, please!)
> >> >>
> >> >>
> >> >> "kevcar40" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > 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
> >> >> >> http://I.Am/DougSteele
> >> >> >> (no e-mails, please!)
> >> >> >>
> >> >> >>
> >> >> >> "kevcar40" <(E-Mail Removed)> wrote in message
> >> >> >> news:(E-Mail Removed)...
> >> >> >> > 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
> >> >> >> >
> >> >> >
> >> >
> >