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
Microsoft AccessVBA

Avatar of undefined
Last Comment
Fabrice Lambert

8/22/2022 - Mon
Fabrice Lambert

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

Open in new window

Ryan Chong

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
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Fabrice Lambert

Side note:
Data Formating and Data Type are not related to each other.
The former is about displaying, the later is about internal storage.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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!
Fabrice Lambert

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.
Gustav Brock

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Peter Groves

ASKER
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
Gustav Brock

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

ASKER
SQL deleted as it contained company info! sorta !

Here it is! (gone)

Pete
Your help has saved me hundreds of hours of internet surfing.
fblack61
Peter Groves

ASKER
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
Gustav Brock

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 Fye

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Fabrice Lambert

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

ASKER
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
Gustav Brock

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Peter Groves

ASKER
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 Fye

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.
Peter Groves

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Peter Groves

ASKER
Thanks everyone!

Pete
Fabrice Lambert

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