Solved Parametrised query: "type conversion failure" when inserting dates, e.g. Now()

[Notiophilus]

Trying to insert a date with a parametrised query, but it always fails: "Microsoft Access set 1 field(s) to Null due a type conversion failure..." I've tried inserting Now() - which is what I want - CDate(01/02/2003), 01/02/2003... nothing. I'm sure the answer is really obvious, but googling has failed and dates always do my head in.

See the attached database for a very simple test table and query.

(Possibly relevant: my system uses the usual DD/MM/YY formatting and I know Access (or just VBA)? uses the United States MM/DD/YY formatting, which can cause trouble.)

 

[Gasman]

INSERT INTO tblTEST ( DateTest )
Values ([SomeDate])

Public Sub TestParameterQuery()
    With CurrentDb.QueryDefs("qryTest")
        .Parameters(0) = Now()
    End With

End Sub

 

[CJ_London]

think there is a bit more to it, the sql you have you cannot set parameters in vba - at least the parameters option on the ribbon is greyed out in the sql window

try

INSERT INTO tblTEST ( DateTest )
SELECT [Somedate]

and your code

Public Sub TestParameterQuery()
'On Error GoTo ErrHandler


    With CurrentDb.QueryDefs("qryTest")
        .Parameters(0) = Now()
        .Execute
    End With
    
End Sub

If you want to use parameters for a values type append, create the query in design view and set the parameter, then go to the sql view and change the select line to values

PARAMETERS somedate DateTime;
INSERT INTO tblTEST ( DateTest )
VALUES ([Somedate]);

Or just include the parameters line when writing your sql

 

[Gasman]

Yes, I missed off the execute, sorry.
Also added the parameters to the query

PARAMETERS SomeDate DateTime;
INSERT INTO tblTEST ( DateTest )
VALUES ([SomeDate]);

If you want to name the parameters

Public Sub TestParameterQuery()
    With CurrentDb.QueryDefs("qryTest")
        .Parameters("SomeDate") = Now() + 1
        .Execute
    End With
   
End Sub

 

[Notiophilus]

YEP, I'd forgotten the .Execute. Like an eejit.
Many thanks!

 

[Pat Hartman]

I've tried inserting Now() - which is what I want

You don't actually want Now(). You want Date().

Now() includes time of day and will not be very satisfactory when used as criteria.

This is what happens if you use Now() in the append query.

 

[Notiophilus]

Thanks! I didn't say, but I'm actually using this in a LogError function, so time of day is quite helpful.