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!

 

[Notiophilus]

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