Text box problem 1

[LATECHmatt05]

I am new to access. Right now I have a form with a text box so that the user can enter a date and a query is called from the form and finds a record with that date. I keep getting runtime error 2001 each time I use the form. It is really weird I wonder do I need to edit my query. I will include the code that is executed each time I click the ok button to submit the date. Any help is very much appreciated. Thank you very much.
Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("masterq")
strSQL = "SELECT Begin.*" & _
"FROM Begin " & _
"WHERE Begin.date='" & Me.[DateA].Value & " ';"
qdf.sql = strSQL
DoCmd.OpenQuery "masterq"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing


End Sub

My query is not a parameter query is this the problem?

 

[scriverb]

When wrapping text strings and cancatenating them together you must be careful of the spaces necessary to create a valid SQL string. Notice the extra space after tge asterick(*). I also removed the extra space before the last single-quote.

strSQL = "SELECT Begin.*[highlight] [/highlight]" & _
             "FROM Begin " & _
             "WHERE Begin.date='" & Me.[DateA].Value & "';"

Post back if you still have problems.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[PHV]

Replace this:
"WHERE Begin.date='" & Me.[DateA].Value & " ';"
By this:
"WHERE Begin.date=#" & Me!DateA & "#;"
provided DateA is properly formatted
else:
"WHERE Begin.date=#" & Format(Me!DateA, "m/d/yyyy") & "#;"

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

 

[LittleSmudge]

Try this

Remove all the text that I've shown Bold

strSQL = "SELECT Begin.*" & _
"FROM Begin " & _
"WHERE Begin.date = #" & Me.[DateA].Value & " #;"


As you only have one table, then the first Begin. is unnecessary.
Because you've used a system work as a field name 'Date' ( always a bad move ) you can't avoid the Begin. in the Where clause

Me. is unnecessary
[ is unnecessary
] is unnecessary
.Value is unnecessary

But the 2 real killers are the absence of a space between the * and FROM and the use of ' instead of # as a date delimiter



What you want is

strSQL = "SELECT * " _
       & "FROM Begin " _
       & "WHERE Begin.date = #" & DateA & " #;"

'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LATECHmatt05]

thank you all very much.
I do have one more thing though how would I write a BETWEEN AND statement in this code if I had another box named DateB?

 

[RoyVidar]

[tt]"WHERE Begin.[date] between #" & Format(Me!DateA, "m/d/yyyy") & _
"# and #" & Format(Me!DateB, "m/d/yyyy") & "#;"[/tt]

- note Date is a reserved word, and should be avoided as name of fields, controls, variables... surrounding with [brackets] makes it a bit better;-)

Roy-Vidar