Run-time error '3075':

T

Toni Miletic

Hi all,

I'm using Access 2000 SR1

Could you please help me with this code. I'm trying to run SQL query from VBA:
VBA code:
strSQL = "SELECT tblUzorak.IDVrsta, Avg([tblUzorak]![PEN14647_6h]) AS
30A_PEN14647_6h, Avg([tblUzorak]![PEN14647_1d]) AS 30A_PEN14647_1d,
Format(Avg([tblUzorak]![pastaENpoc]),""Long Time"",2,2) AS 30A_pastaENpoc,
Avg([tblUzorak]![CA]) AS 30A_CA, 1/Avg([tblUzorak]![C12A7_CA]) AS
30A_CA_C12A7, Avg([tblUzorak]![SiO2]) AS 30A_SiO2, Avg([tblUzorak]![FeO]) AS
30A_FeO, Avg([tblUzorak]![AC]) AS 30A_AC, StDev([tblUzorak]![PEN14647_6h]) AS
30ST_PEN14647_6h, StDev([tblUzorak]![PEN14647_1d]) AS 30ST_PEN14647_1d,
Format(StDev([tblUzorak]![pastaENpoc]),""Long Time"",2,2) AS 30ST_pastaENpoc,
StDev([tblUzorak]![CA]) AS 30ST_CA, 1/StDev([tblUzorak]![C12A7_CA]) AS
30ST_CA_C12A7, StDev([tblUzorak]![SiO2]) AS 30ST_SiO2,
StDev([tblUzorak]![FeO]) AS 30ST_FeO, StDev([tblUzorak]![AC]) AS 30ST_AC " & _
"FROM tblVrsta INNER JOIN tblUzorak ON tblVrsta.ID =
tblUzorak.IDVrsta " & _
"WHERE ((tblVrsta.vrsta) Like '" &
[Forms]![frmStatsitikaOpreme]![VrstaCementa] & "') AND ((tblUzorak.datum)
BETWEEN #" & startdate & "# AND #" & enddate & "# ) AND ((tblUzorak.proces)
Like '" & [Forms]![frmStatsitikaOpreme]![proces] & "') " & _
"GROUP BY tblUzorak.IDVrsta "
Set rstRS = dbsDB.OpenRecordset(strSQL, dbOpenDynaset)

I got this error message after running:
Run-time error '3075':

Syntax error in date in query expresion '((tblVrsta.vrsta) Like 'Product1')
AND ((tblUzorak.datum) BETWEEN #11.10.2009# AND #10.11.2009) AND
((tblUzorak.preces) like 'process1')'.

Any help would be great. Thanks in advance.
Toni
 
T

Toni Miletic

Hi all again me,

If I change regional settings on the PC to English US then everything is
working fine. I was using before Croatian regional settings. Any idea how to
solve tis without changing regional settings?

Kind regards
Toni
 
J

John Spencer

Try changing the clause

AND (tblUzorak.datum BETWEEN #" & startdate & "# AND #" & enddate & "# )
to force the date format into the unambiguous yyyy-mm-dd format
AND (tblUzorak.datum BETWEEN " & Format(startdate,"\#yyyy-mm-dd\#")& " AND
" & Format(enddate,"\#yyyy-mm-dd\#") & " )

Also why are you using LIKE and not the equals comparison operator?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
T

Toni Miletic

Thanks John for your help it's working now with your code even I was in the
meantime fixed it differently.

Instead of this code:
AND (tblUzorak.datum BETWEEN #" & startdate & "# AND #" & enddate & "# )

I used this one:
AND ((tblUzorak.datum) BETWEEN #" & Format(CDate(strStartDateAsText),
"mm\/dd\/yyyy") & "# AND #" & Format(CDate(strEndDateAsText), "mm\/dd\/yyyy")
& "# )

end I put before strSQL string two lines where I transfer my dates
(startdate and enddate) in text format (strStartDateAsText and
strEndDateAsText).

P.S. I was using LIKE instead of equals comparison operator because I will
maybe in the future filter more detailed using * in my expression.

Kind regards
Toni
--
Kind regards
Toni


John Spencer said:
Try changing the clause

AND (tblUzorak.datum BETWEEN #" & startdate & "# AND #" & enddate & "# )
to force the date format into the unambiguous yyyy-mm-dd format
AND (tblUzorak.datum BETWEEN " & Format(startdate,"\#yyyy-mm-dd\#")& " AND
" & Format(enddate,"\#yyyy-mm-dd\#") & " )

Also why are you using LIKE and not the equals comparison operator?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Toni said:
Hi all,

I'm using Access 2000 SR1

Could you please help me with this code. I'm trying to run SQL query from VBA:
VBA code:
strSQL = "SELECT tblUzorak.IDVrsta, Avg([tblUzorak]![PEN14647_6h]) AS
30A_PEN14647_6h, Avg([tblUzorak]![PEN14647_1d]) AS 30A_PEN14647_1d,
Format(Avg([tblUzorak]![pastaENpoc]),""Long Time"",2,2) AS 30A_pastaENpoc,
Avg([tblUzorak]![CA]) AS 30A_CA, 1/Avg([tblUzorak]![C12A7_CA]) AS
30A_CA_C12A7, Avg([tblUzorak]![SiO2]) AS 30A_SiO2, Avg([tblUzorak]![FeO]) AS
30A_FeO, Avg([tblUzorak]![AC]) AS 30A_AC, StDev([tblUzorak]![PEN14647_6h]) AS
30ST_PEN14647_6h, StDev([tblUzorak]![PEN14647_1d]) AS 30ST_PEN14647_1d,
Format(StDev([tblUzorak]![pastaENpoc]),""Long Time"",2,2) AS 30ST_pastaENpoc,
StDev([tblUzorak]![CA]) AS 30ST_CA, 1/StDev([tblUzorak]![C12A7_CA]) AS
30ST_CA_C12A7, StDev([tblUzorak]![SiO2]) AS 30ST_SiO2,
StDev([tblUzorak]![FeO]) AS 30ST_FeO, StDev([tblUzorak]![AC]) AS 30ST_AC " & _
"FROM tblVrsta INNER JOIN tblUzorak ON tblVrsta.ID =
tblUzorak.IDVrsta " & _
"WHERE ((tblVrsta.vrsta) Like '" &
[Forms]![frmStatsitikaOpreme]![VrstaCementa] & "') AND ((tblUzorak.datum)
BETWEEN #" & startdate & "# AND #" & enddate & "# ) AND ((tblUzorak.proces)
Like '" & [Forms]![frmStatsitikaOpreme]![proces] & "') " & _
"GROUP BY tblUzorak.IDVrsta "
Set rstRS = dbsDB.OpenRecordset(strSQL, dbOpenDynaset)

I got this error message after running:
Run-time error '3075':

Syntax error in date in query expresion '((tblVrsta.vrsta) Like 'Product1')
AND ((tblUzorak.datum) BETWEEN #11.10.2009# AND #10.11.2009) AND
((tblUzorak.preces) like 'process1')'.

Any help would be great. Thanks in advance.
Toni
Click to expand...
.
Click to expand...
 
Top