Link to home
Start Free TrialLog in
Avatar of Peter Groves
Peter Groves

asked on

keep getting 3421 errors querydef parameters on date field!

Need to get parameters passed to query but keep getting 3421 error type mismatch!

I have a form that I use to select a start date called STARTDATE an it is formatted as short date which gives todays date as  2018-12-11 09:19:23
In my query I have a criteria that I put as  >[STARTDATE2]   and its format is as DATE/TIME. No matter what combination I try I still get type conversion errors!

Private Sub execute_RAP_Click()
    Dim db              As DAO.Database
    Dim qdf             As DAO.QueryDef
    Dim prm             As DAO.Parameter
    Dim rs              As DAO.Recordset
 Dim results As String
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("ECH_update1210")
 
    qdf.parameters([STARTDATE2]) = Me.STARTDATE
   
    Set rs = qdf.OpenRecordset
 
    'Work with the recordset
 
 
        Do Until rs.EOF
            results = results & rs.Fields("net_num") & "; "
rs.movenext
        Loop

     'Cleanup after ourselves
    Set qdf = Nothing
    Set db = Nothing
   


Set db = Nothing
Set rs = Nothing

End Sub

I've tried several combinations with CDATE() and FORMAT with same results!
Pete
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Me.STARTDATE is a string.
You need to cast it into date first:
qdf.parameters([STARTDATE2]) = CDate(Me.STARTDATE)

Open in new window

Do Until rs.EOF
            results = results & rs.Fields("net_num") & "; "

        Loop

there is no rs.movenext in your loop!

MsgBox (" list item = ") & results

shouldn't it be:

MsgBox "(" list item = ")" & results

Open in new window

?

is STARTDATE2 also a date time field? you want to compare with date and time?
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Side note:
Data Formating and Data Type are not related to each other.
The former is about displaying, the later is about internal storage.
Avatar of Peter Groves
Peter Groves

ASKER

OK now I get run-time error 2465 Microsoft can't find the field 'l1' refered to in your expression.

Pete


P.S. Movenext was left out from copy and The msgbox was extraneous and removed same result!
OK now I get run-time error 2465 Microsoft can't find the field 'l1' refered to in your expression.
Updated code please ? (include the query).
My Crystal ball is broken.
In your query, specify the parameter data type:

PARAMETERS STARTDATE2 DateTime;
SELECT … <your current query>

Open in new window

Also, make sure that you filter on the date field itself, not an expression like Format([DateField], "yyyy-mm-dd") as that returns text.
Hi Gustav, I've set the parameters for the STARTDATE2 as datetime via the design tab the the query builder if thats what you mean!

Thanks

Pete
Yes, that's it.
And the field, you filter on? How about posting the SQL of the query?
SQL deleted as it contained company info! sorta !

Here it is! (gone)

Pete
I don't get errors now as I needed to put the parameters [STARTDATE]  and [Start count]in quotaions !
but the query doesn't return any records. It worked fine as a stand alone query when I replaced STARTDATE and Start count with actual values!

Pete
Seems right. And you had a date format applied to control Me.STARTDATE.

Then this should work:

qdf.Parameters([STARTDATE2]).Value = Me!STARTDATE.Value

Open in new window

If not, your csr_date field may have a time part. It that is the case, try modifying the query with:

HAVING ((Fix(projet1.csr_date) > [STARTDATE2]) AND (projet1.noreseau > [Start count]));

Open in new window

I think aikimark had the right idea.
you need to refer to the parameter by its name (in quotes)

qdf.Parameters("StartDate") = me.StartDate

or by it's numeric place

qdf.Parameters(1) = me.StartDate
Toggle a breakpoint, inspect your variables, and ensure the value given as parameters are the ones expected.
Make sure there are no implicit convertions.
OK the main problem with the errors was the missing quotes, but I still can't get the parameters to
affect the query as I still get 0 records. I've removed the parameter references in the query and replaced
startdate with   >#2018-11-05# and it works fine!  Just can't get it to work using the parameters .

I even tried to put the parameter on another numeric field and same result  no records!

Pete
Then you pass no value to the query. Try:

Debug.Print Me!STARTDATE.Value
qdf.Parameters([STARTDATE2]).Value = #2018-11-05# 

Open in new window

and tell us what Debug.Print is printing.
If I hover over the qdf of qdf.parameters("[STARTDATE]")  is see the correct date!  
I'va also tried the #2018-11-05# with same result!

must be missing something in  a declaration somewhere!

Pete
Change your query so that instead of using your parameter in a criteria (WHERE clause) you use it in the SELECT statement:

Parameters [StartDate] datetime;
SELECT field1, Field2, [StartDate] as TestParam
FROM yourTable

and see what is returned in that field when you open the recordset.
After fixing the quotes and try everything I could think of , turns out I had to compact and repair the database!
Work fine now! God I hate Access for this BS!  

Pete
Thanks everyone!

Pete
God I hate Access for this BS!  
Well, most troubles result from bad design, bad practices, low VBA knowledge ect ...