WHAT IS WRONG

  • Thread starter Thread starter Farman Khan via AccessMonster.com
  • Start date Start date
F

Farman Khan via AccessMonster.com

here is the code i m using to test a FORM.FIELD BETWEEN TABLE.FIELDS

SELECT * FROM rentals WHERE (#" & Me.txtfrom & "# >= from_date) AND (#" &
Me.txtfrom & "# < exp_return) AND (carreg='" & Me.txtcarreg & "')"

and it generates the following error

Run-time error '3075':
Syntax error in date in query expression'(#01.06.2006#>= fromdate) AND
(#01.06.2006#<exp_date) AND (carreg='2')'.
 
here is the code i m using to test a FORM.FIELD BETWEEN TABLE.FIELDS
SELECT * FROM rentals WHERE (#" & Me.txtfrom & "# >= from_date) AND (#" &
Me.txtfrom & "# < exp_return) AND (carreg='" & Me.txtcarreg & "')"

and it generates the following error

Run-time error '3075':
Syntax error in date in query expression'(#01.06.2006#>= fromdate) AND
(#01.06.2006#<exp_date) AND (carreg='2')'.
Click to expand...

Are you sure you have the decimal point as a date delimiter? Check your
International settings.
You might have to use slashes.

Tom Lake
 
Farman,

Any chance field carreg is numeric? If yes, you need to remove the
single quotes around it:

.... AND (carreg=" & Me.txtcarreg & ")"

HTH,
Nikos
 
Farman Khan via AccessMonster.com said:
here is the code i m using to test a FORM.FIELD BETWEEN TABLE.FIELDS

SELECT * FROM rentals WHERE (#" & Me.txtfrom & "# >= from_date) AND (#" &
Me.txtfrom & "# < exp_return) AND (carreg='" & Me.txtcarreg & "')"

and it generates the following error

Run-time error '3075':
Syntax error in date in query expression'(#01.06.2006#>= fromdate) AND
(#01.06.2006#<exp_date) AND (carreg='2')'.
Click to expand...

One way to determine where the error occurs is to try replacing the Select
statement with:

"SELECT * FROM rentals WHERE carreg='" & Me.txtcarreg & "' "

If that works and the expected records are returned, try:

"SELECT * FROM rentals WHERE #" & Me.txtfrom & "# >= from_date"

Then try:

"SELECT * FROM rentals WHERE #" & Me.txtfrom & "# < exp_return"


Once you get all three 'Selects' to work, put the 'Where' clause back
together.
 
Also I just noticed you are showing in your select statement

.......& "# >= from_date) AND ......


But the error message shows:

....... (#01.06.2006#>= fromdate) AND ......

ie. no underscore in "fromdate"


Is it "from_date" or "fromdate" ? What is the field name in the table?
 
yes i have changed the delimeter to dot from my regional setting but i
found out that only american standard date format can be used in the SQL in
VBA , that means it does not support any other format so i had to use the
FORMAT function to change the format in the SQL statement in VBA, i think
they should do something to it so that other formats work with it too, dont
u think they should?
 
the field carreg is a number which is a car registration, that should be
alphanumeric
well thanx friend i solved that statement
 
Back
Top