lost in error 3075

J

javablood

Help! I am trying to modify another's code to use a form to input a date
(datbegin) in a query (see below) instead of having to manually change it.
I dimensioned datbegin as Date and set datebegin =
[Forms]![frmMK]![lbodatbegin] and with MsgBox I know that part is working.
However, I get the 3075 error pointing to this:

Set rstMKTable = db.OpenRecordset(strQry)

I did this Dim rstMKTable As DAO.Recordset, hopefully correctly from
postings re error 3061.



strQry = "SELECT ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE,
ACME.VALUE, ACME.FLAG, ACME.UNITS, " & _
"ACME.QC, ACME.[SELECT], ACME.MEDIUM, IIf([acme].[flag] Like " &
Chr(34) & "*U*" & Chr(34) & ",0.1*[VALUE], [VALUE])" & _
"AS [MK Use Value] FROM MK_Stations INNER JOIN ((ACME INNER JOIN
ACME_PAR ON ACME.PARAM_ID = ACME_PAR.PARAM_ID) " & _
"INNER JOIN MK_PARAM ON ACME_PAR.PARAM_ID = MK_PARAM.PARAM_ID)
ON MK_Stations.STATION_ID = ACME.STATION_ID " & _
" WHERE (((ACME.STATION_ID)=" & Chr(34) & arrWells(i) & Chr(34)
& ") " & _
"AND ((ACME_PAR.PARAM_NAME)=" & Chr(34) & arrParam(j) & Chr(34)
& ") " & _
"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _
"AND ((ACME.QC) Not Like " & Chr(34) & "*C*" & Chr(34) & ") " & _
"AND ((ACME.[SELECT])=-1) AND ((ACME.MEDIUM)=" & Chr(34) & "gw"
& Chr(34) & ") " & _
"ORDER BY ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE; "

Any help for a novice programmer is much appreciated! Thanks,
 
D

Dirk Goldgar

javablood said:
Help! I am trying to modify another's code to use a form to input a date
(datbegin) in a query (see below) instead of having to manually change it.
I dimensioned datbegin as Date and set datebegin =
[Forms]![frmMK]![lbodatbegin] and with MsgBox I know that part is working.
However, I get the 3075 error pointing to this:

Set rstMKTable = db.OpenRecordset(strQry)

I did this Dim rstMKTable As DAO.Recordset, hopefully correctly from
postings re error 3061.



strQry = "SELECT ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE,
ACME.VALUE, ACME.FLAG, ACME.UNITS, " & _
"ACME.QC, ACME.[SELECT], ACME.MEDIUM, IIf([acme].[flag] Like "
&
Chr(34) & "*U*" & Chr(34) & ",0.1*[VALUE], [VALUE])" & _
"AS [MK Use Value] FROM MK_Stations INNER JOIN ((ACME INNER
JOIN
ACME_PAR ON ACME.PARAM_ID = ACME_PAR.PARAM_ID) " & _
"INNER JOIN MK_PARAM ON ACME_PAR.PARAM_ID = MK_PARAM.PARAM_ID)
ON MK_Stations.STATION_ID = ACME.STATION_ID " & _
" WHERE (((ACME.STATION_ID)=" & Chr(34) & arrWells(i) & Chr(34)
& ") " & _
"AND ((ACME_PAR.PARAM_NAME)=" & Chr(34) & arrParam(j) & Chr(34)
& ") " & _
"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _
"AND ((ACME.QC) Not Like " & Chr(34) & "*C*" & Chr(34) & ") " &
_
"AND ((ACME.[SELECT])=-1) AND ((ACME.MEDIUM)=" & Chr(34) & "gw"
& Chr(34) & ") " & _
"ORDER BY ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE;
"

Any help for a novice programmer is much appreciated! Thanks,
Click to expand...


It would have been a good idea to post the exect error message you got, too,
as that would have given some indication of where in the query the error
was. However, I can see that this:
"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _
Click to expand...

.... is wrong, as it has the variable name inside the quotes. Try replacing
that line with this:

"AND ((ACME.SAMP_DATE)> #" & _
Format(datbegin, "mm\/dd\/yyyy") & "#)) " & _
 
J

javablood

Dirk,

Yes sorry it was a syntax error with the date as you pointed out. I tried
variations of quotes and then found something in John Spencer's 11.10.09
response to a similar question to get me this:

"AND ((ACME.SAMP_DATE)> " & Format(datbegin, "\#yyyy-mm-dd\#") & ")) " & _

and it worked. It is similar to yours but with a different format for the
date!

Thanks so much!
--
javablood


Dirk Goldgar said:
javablood said:
Help! I am trying to modify another's code to use a form to input a date
(datbegin) in a query (see below) instead of having to manually change it.
I dimensioned datbegin as Date and set datebegin =
[Forms]![frmMK]![lbodatbegin] and with MsgBox I know that part is working.
However, I get the 3075 error pointing to this:

Set rstMKTable = db.OpenRecordset(strQry)

I did this Dim rstMKTable As DAO.Recordset, hopefully correctly from
postings re error 3061.



strQry = "SELECT ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE,
ACME.VALUE, ACME.FLAG, ACME.UNITS, " & _
"ACME.QC, ACME.[SELECT], ACME.MEDIUM, IIf([acme].[flag] Like "
&
Chr(34) & "*U*" & Chr(34) & ",0.1*[VALUE], [VALUE])" & _
"AS [MK Use Value] FROM MK_Stations INNER JOIN ((ACME INNER
JOIN
ACME_PAR ON ACME.PARAM_ID = ACME_PAR.PARAM_ID) " & _
"INNER JOIN MK_PARAM ON ACME_PAR.PARAM_ID = MK_PARAM.PARAM_ID)
ON MK_Stations.STATION_ID = ACME.STATION_ID " & _
" WHERE (((ACME.STATION_ID)=" & Chr(34) & arrWells(i) & Chr(34)
& ") " & _
"AND ((ACME_PAR.PARAM_NAME)=" & Chr(34) & arrParam(j) & Chr(34)
& ") " & _
"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _
"AND ((ACME.QC) Not Like " & Chr(34) & "*C*" & Chr(34) & ") " &
_
"AND ((ACME.[SELECT])=-1) AND ((ACME.MEDIUM)=" & Chr(34) & "gw"
& Chr(34) & ") " & _
"ORDER BY ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE;
"

Any help for a novice programmer is much appreciated! Thanks,
Click to expand...


It would have been a good idea to post the exect error message you got, too,
as that would have given some indication of where in the query the error
was. However, I can see that this:
"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _
Click to expand...

... is wrong, as it has the variable name inside the quotes. Try replacing
that line with this:

"AND ((ACME.SAMP_DATE)> #" & _
Format(datbegin, "mm\/dd\/yyyy") & "#)) " & _


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
J

javablood

Dirk,

Just as a point of clarification (in case someone else needs this), the
following worked:

"AND ((ACME.SAMP_DATE)> #" & Format(datbegin, "mm\/dd\/yyyy") & "#)) " & _

You forgot the & in front of Format but that is because you can write stuff
like this in your sleep and I can only dream of doing so!

Thanks again!
--
javablood


Dirk Goldgar said:
javablood said:
Help! I am trying to modify another's code to use a form to input a date
(datbegin) in a query (see below) instead of having to manually change it.
I dimensioned datbegin as Date and set datebegin =
[Forms]![frmMK]![lbodatbegin] and with MsgBox I know that part is working.
However, I get the 3075 error pointing to this:

Set rstMKTable = db.OpenRecordset(strQry)

I did this Dim rstMKTable As DAO.Recordset, hopefully correctly from
postings re error 3061.



strQry = "SELECT ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE,
ACME.VALUE, ACME.FLAG, ACME.UNITS, " & _
"ACME.QC, ACME.[SELECT], ACME.MEDIUM, IIf([acme].[flag] Like "
&
Chr(34) & "*U*" & Chr(34) & ",0.1*[VALUE], [VALUE])" & _
"AS [MK Use Value] FROM MK_Stations INNER JOIN ((ACME INNER
JOIN
ACME_PAR ON ACME.PARAM_ID = ACME_PAR.PARAM_ID) " & _
"INNER JOIN MK_PARAM ON ACME_PAR.PARAM_ID = MK_PARAM.PARAM_ID)
ON MK_Stations.STATION_ID = ACME.STATION_ID " & _
" WHERE (((ACME.STATION_ID)=" & Chr(34) & arrWells(i) & Chr(34)
& ") " & _
"AND ((ACME_PAR.PARAM_NAME)=" & Chr(34) & arrParam(j) & Chr(34)
& ") " & _
"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _
"AND ((ACME.QC) Not Like " & Chr(34) & "*C*" & Chr(34) & ") " &
_
"AND ((ACME.[SELECT])=-1) AND ((ACME.MEDIUM)=" & Chr(34) & "gw"
& Chr(34) & ") " & _
"ORDER BY ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE;
"

Any help for a novice programmer is much appreciated! Thanks,
Click to expand...


It would have been a good idea to post the exect error message you got, too,
as that would have given some indication of where in the query the error
was. However, I can see that this:
"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _
Click to expand...

... is wrong, as it has the variable name inside the quotes. Try replacing
that line with this:

"AND ((ACME.SAMP_DATE)> #" & _
Format(datbegin, "mm\/dd\/yyyy") & "#)) " & _


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

javablood said:
Dirk,

Yes sorry it was a syntax error with the date as you pointed out. I tried
variations of quotes and then found something in John Spencer's 11.10.09
response to a similar question to get me this:

"AND ((ACME.SAMP_DATE)> " & Format(datbegin, "\#yyyy-mm-dd\#") & ")) " & _

and it worked. It is similar to yours but with a different format for the
date!
Click to expand...


Yes, that will also work. The date literal must be enclosed in the '#'
characters, and it must either be unambiguous, or in the ISO standard
YYYY-MM-DD format as above, or in standard US format, MM/DD/YYYY, as in my
own suggestion.

You can use the format expression to add the '#' delimiters, as above, or
you can build them into the surrounding text literals yourself.
 
J

javablood

Roger,

I tried that and it did not work. It wanted the double quotes as in

"AND ((ACME.SAMP_DATE)> #"" & datbegin & ""#)) " & _

but I still got the 3075 sytax in date error.

thanks,
--
javablood


Roger Carlson said:
3075 is a syntax error in your SQL string. In your case, this line:

"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _

is missing proper string delimiters. It should be this:

"AND ((ACME.SAMP_DATE)> #" & datbegin & "#)) " & _

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


javablood said:
Help! I am trying to modify another's code to use a form to input a date
(datbegin) in a query (see below) instead of having to manually change it.
I dimensioned datbegin as Date and set datebegin =
[Forms]![frmMK]![lbodatbegin] and with MsgBox I know that part is working.
However, I get the 3075 error pointing to this:

Set rstMKTable = db.OpenRecordset(strQry)

I did this Dim rstMKTable As DAO.Recordset, hopefully correctly from
postings re error 3061.



strQry = "SELECT ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE,
ACME.VALUE, ACME.FLAG, ACME.UNITS, " & _
"ACME.QC, ACME.[SELECT], ACME.MEDIUM, IIf([acme].[flag] Like "
&
Chr(34) & "*U*" & Chr(34) & ",0.1*[VALUE], [VALUE])" & _
"AS [MK Use Value] FROM MK_Stations INNER JOIN ((ACME INNER
JOIN
ACME_PAR ON ACME.PARAM_ID = ACME_PAR.PARAM_ID) " & _
"INNER JOIN MK_PARAM ON ACME_PAR.PARAM_ID = MK_PARAM.PARAM_ID)
ON MK_Stations.STATION_ID = ACME.STATION_ID " & _
" WHERE (((ACME.STATION_ID)=" & Chr(34) & arrWells(i) & Chr(34)
& ") " & _
"AND ((ACME_PAR.PARAM_NAME)=" & Chr(34) & arrParam(j) & Chr(34)
& ") " & _
"AND ((ACME.SAMP_DATE)> #&datbegin&#)) " & _
"AND ((ACME.QC) Not Like " & Chr(34) & "*C*" & Chr(34) & ") " &
_
"AND ((ACME.[SELECT])=-1) AND ((ACME.MEDIUM)=" & Chr(34) & "gw"
& Chr(34) & ") " & _
"ORDER BY ACME.STATION_ID, ACME_PAR.PARAM_NAME, ACME.SAMP_DATE;
"

Any help for a novice programmer is much appreciated! Thanks,
Click to expand...


.
Click to expand...
 
J

javablood

Dirk,

Thanks, I knew about the # but was unaware of the strict format required for
the date.
 
J

javablood

Dirk,

Thanks. I knew about the # but did not realize the strict requirement for
the date format. Now I do!
 
Top