Enter Parameter Value "What did I Miss" 1

[tbonehwd]

Ok this is driving me nuts on my form I have a field called txt.FRMDATE Where I am trying to enter a date and run this code however every time I enter the date I get the famous Enter Parameter Value then if I enter the date again it works fine. I know I am missing some little thing if you see it please let me know.

Dim strFRMDATE As String
Dim strTEST As String
Dim strTEST1 As String
Dim strTEST2 As String
strFRMDATE = "#" & Me.txtFRMDATE.Value & "#"
 strTEST = "12/4/2005"
 strTEST1 = "11/27/2005"
 strTEST2 = "11/20/2005"
"SELECT DATA.ISBN, DATA.CYP_UNITS_SOLD AS [" & strTEST & "] " & _
", DATA_1.CYP_UNITS_SOLD AS [" & strTEST1 & "]" & _
", DATA_2.CYP_UNITS_SOLD AS [" & strTEST2 & "] " & _
"FROM ((DATA LEFT JOIN DATA AS DATA_1 ON DATA.ISBN=DATA_1.ISBN) " & _
"LEFT JOIN DATA AS DATA_2 ON DATA.ISBN=DATA_2.ISBN)" & _
"WHERE ((DATA.WEEKNO)=DATEPART('ww',[" & strFRMDATE & "])) " & _
"AND ((DATA_1.WEEKNO)=DATEPART('ww',[" & strFRMDATE & "])-1) " & _
"AND ((DATA_2.WEEKNO)=DATEPART('ww',[" & strFRMDATE & "])-2) " & _
"AND DATA.CMT_CODE='AMZ' " & _
"AND DATA_1.CMT_CODE='AMZ' " & _
"AND DATA_2.CMT_CODE='AMZ'" & _
"AND ((YEAR(DATA.WEEK))=YEAR([" & strFRMDATE & "]))" & _
"AND ((YEAR(DATA_1.WEEK))=YEAR([" & strFRMDATE & "]))" & _
"AND ((YEAR(DATA_2.WEEK))=YEAR([" & strFRMDATE & "]))" & _
";"

 

[PHV]

WHERE ((DATA.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")) " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

I had tried that before and didn't work so I tried it again just to make sure and I get an error 3075 missing operator in query expression

 

[PHV]

What is the content of Me.txtFRMDATE.Value when you build the SQL ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

12/06/2005

 

[PHV]

And what is the output in the immediate window (Ctrl-G) of the following instruction ?
Debug.Print yourSQLstring

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

PHV I have been asked this before I am not sure how to do what you are saying I have never been able to step through code and see what is happening (CTRL-G) opens the code window but does not put in the parameters. (Im sure a dumb question)

However I did do this if it helps

SELECT DATA.ISBN, DATA.CYP_UNITS_SOLD AS [12/4/2005], DATA_1.CYP_UNITS_SOLD AS [11/27/2005], DATA_2.CYP_UNITS_SOLD AS [11/20/2005]
FROM (DATA LEFT JOIN DATA AS DATA_1 ON DATA.ISBN = DATA_1.ISBN) LEFT JOIN DATA AS DATA_2 ON DATA.ISBN = DATA_2.ISBN
WHERE (((DATA.WEEKNO)=DatePart('ww',[=#12/06/2005#])) AND ((DATA_1.WEEKNO)=DatePart('ww',[=#12/06/2005#])-1) AND ((DATA_2.WEEKNO)=DatePart('ww',[=#12/06/2005#])-2) AND ((DATA.CMT_CODE)='AMZ') AND ((DATA_1.CMT_CODE)='AMZ') AND ((DATA_2.CMT_CODE)='AMZ') AND ((Year([DATA].[WEEK]))=Year([=#12/06/2005#])) AND ((Year([DATA_1].[WEEK]))=Year([=#12/06/2005#])) AND ((Year([DATA_2].[WEEK]))=Year([=#12/06/2005#])));

 

[PHV]

As I suggested you, get rid of the brackets around strFRMDATE and where is the equal sign coming from ?
This:
DatePart('ww',[=#12/06/2005#])
Should be:
DatePart('ww',#12/06/2005#)

(6 replaces to do)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

I must be brain dead but it know works here is the code corrected and working

PHV - Is the BEST!!!!!!!!!!!!!!!!!!!!!!!

Dim strFRMDATE As String
Dim strTEST As String
Dim strTEST1 As String
Dim strTEST2 As String
strFRMDATE = "#" & Me.txtFRMDATE.Value & "#"
 strTEST = "12/4/2005"
 strTEST1 = "11/27/2005"
 strTEST2 = "11/20/2005"
"SELECT DATA.ISBN, DATA.CYP_UNITS_SOLD AS [" & strTEST & "] " & _
", DATA_1.CYP_UNITS_SOLD AS [" & strTEST1 & "]" & _
", DATA_2.CYP_UNITS_SOLD AS [" & strTEST2 & "] " & _
"FROM ((DATA LEFT JOIN DATA AS DATA_1 ON DATA.ISBN=DATA_1.ISBN) " & _
"LEFT JOIN DATA AS DATA_2 ON DATA.ISBN=DATA_2.ISBN)" & _
"WHERE ((DATA.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")) " & _
"AND ((DATA_1.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")-1) " & _
"AND ((DATA_2.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")-2) " & _
"AND DATA.CMT_CODE='AMZ' " & _
"AND DATA_1.CMT_CODE='AMZ' " & _
"AND DATA_2.CMT_CODE='AMZ'" & _
"AND ((YEAR(DATA.WEEK))=YEAR(" & strFRMDATE & "))" & _
"AND ((YEAR(DATA_1.WEEK))=YEAR(" & strFRMDATE & "))" & _
"AND ((YEAR(DATA_2.WEEK))=YEAR(" & strFRMDATE & "))" & _
";"