keep getting 3421 errors querydef  parameters on date field!

Peter Groves
Peter Groves used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

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

Open in new window

Ryan ChongSoftware Team Lead

Commented:
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?
Top Expert 2014
Commented:
qdf.parameters("STARTDATE2") = Me.STARTDATE

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Side note:
Data Formating and Data Type are not related to each other.
The former is about displaying, the later is about internal storage.

Author

Commented:
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!
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, that's it.
And the field, you filter on? How about posting the SQL of the query?

Author

Commented:
SQL deleted as it contained company info! sorta !

Here it is! (gone)

Pete

Author

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Toggle a breakpoint, inspect your variables, and ensure the value given as parameters are the ones expected.
Make sure there are no implicit convertions.

Author

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Author

Commented:
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

Author

Commented:
Thanks everyone!

Pete
Fabrice LambertConsulting
Distinguished Expert 2017

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial