Run-time error '3075':

[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

 

[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

 

[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

 

[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

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

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

.