WHAT IS WRONG

  • Thread starter Farman Khan via AccessMonster.com
  • 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')'.
 
T

Tom Lake

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
 
N

Nikos Yannacopoulos

Farman,

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

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

HTH,
Nikos
 
S

SteveS

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.
 
S

SteveS

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?
 
F

Farman Khan via AccessMonster.com

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?
 
F

Farman Khan via AccessMonster.com

the field carreg is a number which is a car registration, that should be
alphanumeric
well thanx friend i solved that statement
 
Top