Access 2007 Runtime Error 3075

[Linda]

I'm really confused. I have two databases which use similar code to build a
select statement. One has been working (DB1), the other has not(DB2). As I
was debugging DB2, I brought up DB1 to compare the VB code. Suddenly DB1
started showing the same error. I tried DB1 on another computer and it still
works fine there. The database resides on a network server so I am testing
the same code. The error message is :
Run-time error 3075
Syntax error in date in query expression '[SR Number]='09-0035' AND [Date]
BETWEEN #_/_/_# AND #_/_/_'.

The code looks like:
sWhereClause = sWhereClause & " AND [Date] BETWEEN #" & _
sStart & "# AND #" & sEnd & "#"

I've looked at regional settings on both computers and they are the same.
Any ideas of what might be different between the computers to make this run
differently or what might change underfoot so that the code works and then
suddenly doesn't on the same machine?
Thanks in advance for your help.

 

[Douglas J. Steele]

Exactly what is in sWhereClause when the problem occurs?

One thing I've found is that it's always a good idea to enclose the BETWEEN
clause in parentheses. It's also a good idea to force the date format, since
you should never assume anything about the user's Short Date settings.

sWhereClause = sWhereClause & " AND ([Date] BETWEEN " & _
Format(sStart, "\#yyyy\-mm\-dd\#") & " AND " & _
Format(sEnd, "\#yyyy\-mm\-dd\#") & ")"

And even though you've enclosed the field name Date in square brackets, you
really would be much better off using a different name for the field.

 

[Linda]

I changed Date to be SRDate and enclose the BETWEEN clause in parentheses and
get the same error. The date fields both have an input mask defined in the
properties of 99/99/00;0;_
Essentially what I have is a form with several combo boxes, a text box (for
a title) and then a box for start date and one for end date. The user can
put information into any of the fields to build a query to retrieve records
they want to work on. A similar method was used for another database and it
works fine, but I can't see where I'm going wrong in this one. I am getting
the 3075 error whether or not I try to make an entry in the date fields.
These should not be looked at unless they have values in them so it seems it
is getting a default of _/_/_ which means the field is not null therefore is
being included in the where clause. Does the input mask make an entry in the
field even if there is no input?
I'm not sure if its related but the focus is on the End Date field when the
form finishes loading. I have set the tab order to where the start date
should be 7 and the end date is 8. Any idea why it is not going to tab stop
0 first?
The whereclause = '[SR Number] = '09-0026' AND ([SRDate] BETWEEN #_/_/_# AND
#_/_/_#'

I still don't understand why it works on the same database on one computer
and not the other!
Thank you.
--
Linda

Exactly what is in sWhereClause when the problem occurs?

One thing I've found is that it's always a good idea to enclose the BETWEEN
clause in parentheses. It's also a good idea to force the date format, since
you should never assume anything about the user's Short Date settings.

sWhereClause = sWhereClause & " AND ([Date] BETWEEN " & _
Format(sStart, "\#yyyy\-mm\-dd\#") & " AND " & _
Format(sEnd, "\#yyyy\-mm\-dd\#") & ")"

And even though you've enclosed the field name Date in square brackets, you
really would be much better off using a different name for the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm really confused. I have two databases which use similar code to build
a
select statement. One has been working (DB1), the other has not(DB2). As
I
was debugging DB2, I brought up DB1 to compare the VB code. Suddenly DB1
started showing the same error. I tried DB1 on another computer and it
still
works fine there. The database resides on a network server so I am
testing
the same code. The error message is :
Run-time error 3075
Syntax error in date in query expression '[SR Number]='09-0035' AND [Date]
BETWEEN #_/_/_# AND #_/_/_'.

The code looks like:
sWhereClause = sWhereClause & " AND [Date] BETWEEN #" & _
sStart & "# AND #" & sEnd & "#"

I've looked at regional settings on both computers and they are the same.
Any ideas of what might be different between the computers to make this
run
differently or what might change underfoot so that the code works and then
suddenly doesn't on the same machine?
Thanks in advance for your help.

.

 

[Douglas J. Steele]

Are you checking to see whether the dates have values?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I changed Date to be SRDate and enclose the BETWEEN clause in parentheses
and
get the same error. The date fields both have an input mask defined in the
properties of 99/99/00;0;_
Essentially what I have is a form with several combo boxes, a text box
(for
a title) and then a box for start date and one for end date. The user can
put information into any of the fields to build a query to retrieve
records
they want to work on. A similar method was used for another database and
it
works fine, but I can't see where I'm going wrong in this one. I am
getting
the 3075 error whether or not I try to make an entry in the date fields.
These should not be looked at unless they have values in them so it seems
it
is getting a default of _/_/_ which means the field is not null therefore
is
being included in the where clause. Does the input mask make an entry in
the
field even if there is no input?
I'm not sure if its related but the focus is on the End Date field when
the
form finishes loading. I have set the tab order to where the start date
should be 7 and the end date is 8. Any idea why it is not going to tab
stop
0 first?
The whereclause = '[SR Number] = '09-0026' AND ([SRDate] BETWEEN #_/_/_#
AND
#_/_/_#'

I still don't understand why it works on the same database on one computer
and not the other!
Thank you.
--
Linda

Exactly what is in sWhereClause when the problem occurs?

One thing I've found is that it's always a good idea to enclose the
BETWEEN
clause in parentheses. It's also a good idea to force the date format,
since
you should never assume anything about the user's Short Date settings.

sWhereClause = sWhereClause & " AND ([Date] BETWEEN " & _
Format(sStart, "\#yyyy\-mm\-dd\#") & " AND " & _
Format(sEnd, "\#yyyy\-mm\-dd\#") & ")"

And even though you've enclosed the field name Date in square brackets,
you
really would be much better off using a different name for the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm really confused. I have two databases which use similar code to
build
a
select statement. One has been working (DB1), the other has not(DB2).
As
I
was debugging DB2, I brought up DB1 to compare the VB code. Suddenly
DB1
started showing the same error. I tried DB1 on another computer and it
still
works fine there. The database resides on a network server so I am
testing
the same code. The error message is :
Run-time error 3075
Syntax error in date in query expression '[SR Number]='09-0035' AND
[Date]
BETWEEN #_/_/_# AND #_/_/_'.

The code looks like:
sWhereClause = sWhereClause & " AND [Date] BETWEEN #" & _
sStart & "# AND #" & sEnd & "#"

I've looked at regional settings on both computers and they are the
same.
Any ideas of what might be different between the computers to make this
run
differently or what might change underfoot so that the code works and
then
suddenly doesn't on the same machine?
Thanks in advance for your help.

.

 

[Linda]

I'm checking with the following statement:
If StartDate.Text <> """
and the code drops through i.e. StartDate.Text = ""

I seem to have gotten past this point. Now I am getting a prompt to Enter
Parameter Value for Date. I thought I had changed the references from Date
to SRDate.

Any suggestions on how to find where it is looking for Date?
--
Linda

Are you checking to see whether the dates have values?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I changed Date to be SRDate and enclose the BETWEEN clause in parentheses
and
get the same error. The date fields both have an input mask defined in the
properties of 99/99/00;0;_
Essentially what I have is a form with several combo boxes, a text box
(for
a title) and then a box for start date and one for end date. The user can
put information into any of the fields to build a query to retrieve
records
they want to work on. A similar method was used for another database and
it
works fine, but I can't see where I'm going wrong in this one. I am
getting
the 3075 error whether or not I try to make an entry in the date fields.
These should not be looked at unless they have values in them so it seems
it
is getting a default of _/_/_ which means the field is not null therefore
is
being included in the where clause. Does the input mask make an entry in
the
field even if there is no input?
I'm not sure if its related but the focus is on the End Date field when
the
form finishes loading. I have set the tab order to where the start date
should be 7 and the end date is 8. Any idea why it is not going to tab
stop
0 first?
The whereclause = '[SR Number] = '09-0026' AND ([SRDate] BETWEEN #_/_/_#
AND
#_/_/_#'

I still don't understand why it works on the same database on one computer
and not the other!
Thank you.
--
Linda

Exactly what is in sWhereClause when the problem occurs?

One thing I've found is that it's always a good idea to enclose the
BETWEEN
clause in parentheses. It's also a good idea to force the date format,
since
you should never assume anything about the user's Short Date settings.

sWhereClause = sWhereClause & " AND ([Date] BETWEEN " & _
Format(sStart, "\#yyyy\-mm\-dd\#") & " AND " & _
Format(sEnd, "\#yyyy\-mm\-dd\#") & ")"

And even though you've enclosed the field name Date in square brackets,
you
really would be much better off using a different name for the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I'm really confused. I have two databases which use similar code to
build
a
select statement. One has been working (DB1), the other has not(DB2).
As
I
was debugging DB2, I brought up DB1 to compare the VB code. Suddenly
DB1
started showing the same error. I tried DB1 on another computer and it
still
works fine there. The database resides on a network server so I am
testing
the same code. The error message is :
Run-time error 3075
Syntax error in date in query expression '[SR Number]='09-0035' AND
[Date]
BETWEEN #_/_/_# AND #_/_/_'.

The code looks like:
sWhereClause = sWhereClause & " AND [Date] BETWEEN #" & _
sStart & "# AND #" & sEnd & "#"

I've looked at regional settings on both computers and they are the
same.
Any ideas of what might be different between the computers to make this
run
differently or what might change underfoot so that the code works and
then
suddenly doesn't on the same machine?
Thanks in advance for your help.

.

 

[Linda]

Nevermind, I found it! Thanks for all your help!!
--
Linda

Are you checking to see whether the dates have values?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I changed Date to be SRDate and enclose the BETWEEN clause in parentheses
and
get the same error. The date fields both have an input mask defined in the
properties of 99/99/00;0;_
Essentially what I have is a form with several combo boxes, a text box
(for
a title) and then a box for start date and one for end date. The user can
put information into any of the fields to build a query to retrieve
records
they want to work on. A similar method was used for another database and
it
works fine, but I can't see where I'm going wrong in this one. I am
getting
the 3075 error whether or not I try to make an entry in the date fields.
These should not be looked at unless they have values in them so it seems
it
is getting a default of _/_/_ which means the field is not null therefore
is
being included in the where clause. Does the input mask make an entry in
the
field even if there is no input?
I'm not sure if its related but the focus is on the End Date field when
the
form finishes loading. I have set the tab order to where the start date
should be 7 and the end date is 8. Any idea why it is not going to tab
stop
0 first?
The whereclause = '[SR Number] = '09-0026' AND ([SRDate] BETWEEN #_/_/_#
AND
#_/_/_#'

I still don't understand why it works on the same database on one computer
and not the other!
Thank you.
--
Linda

Exactly what is in sWhereClause when the problem occurs?

One thing I've found is that it's always a good idea to enclose the
BETWEEN
clause in parentheses. It's also a good idea to force the date format,
since
you should never assume anything about the user's Short Date settings.

sWhereClause = sWhereClause & " AND ([Date] BETWEEN " & _
Format(sStart, "\#yyyy\-mm\-dd\#") & " AND " & _
Format(sEnd, "\#yyyy\-mm\-dd\#") & ")"

And even though you've enclosed the field name Date in square brackets,
you
really would be much better off using a different name for the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I'm really confused. I have two databases which use similar code to
build
a
select statement. One has been working (DB1), the other has not(DB2).
As
I
was debugging DB2, I brought up DB1 to compare the VB code. Suddenly
DB1
started showing the same error. I tried DB1 on another computer and it
still
works fine there. The database resides on a network server so I am
testing
the same code. The error message is :
Run-time error 3075
Syntax error in date in query expression '[SR Number]='09-0035' AND
[Date]
BETWEEN #_/_/_# AND #_/_/_'.

The code looks like:
sWhereClause = sWhereClause & " AND [Date] BETWEEN #" & _
sStart & "# AND #" & sEnd & "#"

I've looked at regional settings on both computers and they are the
same.
Any ideas of what might be different between the computers to make this
run
differently or what might change underfoot so that the code works and
then
suddenly doesn't on the same machine?
Thanks in advance for your help.

.