Can't get around error when using an InputBox...any ideas??

[SpyderMan1234]

I have an input box that accepts a Docket Number (among other things) via an input message box. The code looks like this:

MyDocketNo = InputBox("Please enter a valid Docket Number for this case.", "Enter Docket Number")

When I later try to INSERT this mydocketno value into a table using an INSERT statement, I get an error.

The SQL I use is this:

INSERT INTO Casetbl (County, OpenDate, LawBaseNo, DocketNo, Notes)" _
& " SELECT Suspensetbl.County, Date() as EXPR1, " & MyLawBase & "," & MyDocketNo & ", Suspensetbl.Notes" _
& " FROM Suspensetbl WHERE (((Suspensetbl.SuspenseID)=" & MySusID & "));"

And the error is this:
Runtime error 2766
The object doesn't contain the automation object 'M02'

I know the reason that I'm getting the error is because a docket number has hypens that get misinterpreted by the SQL. For example, the error above would be the result of entering something like M02-EF343FE-FEF3. My question is how do I get around this? Any suggestions?

Thanks

 

[PHV]

Replace this:
[tt]& "," & MyDocketNo & ",[/tt]
with this:
[tt]& ",'" & MyDocketNo & "',[/tt]

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