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
Peter GrovesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
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 LeadCommented:
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?
aikimarkCommented:
qdf.parameters("STARTDATE2") = Me.STARTDATE

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Fabrice LambertConsultingCommented:
Side note:
Data Formating and Data Type are not related to each other.
The former is about displaying, the later is about internal storage.
Peter GrovesAuthor 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 LambertConsultingCommented:
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 BrockCIOCommented:
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.
Peter GrovesAuthor 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
Gustav BrockCIOCommented:
Yes, that's it.
And the field, you filter on? How about posting the SQL of the query?
Peter GrovesAuthor Commented:
SQL deleted as it contained company info! sorta !

Here it is! (gone)

Pete
Peter GrovesAuthor 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
Gustav BrockCIOCommented:
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, Developing Solutions LLCCommented:
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 LambertConsultingCommented:
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 GrovesAuthor 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
Gustav BrockCIOCommented:
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.
Peter GrovesAuthor 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, Developing Solutions LLCCommented:
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 GrovesAuthor 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
Peter GrovesAuthor Commented:
Thanks everyone!

Pete
Fabrice LambertConsultingCommented:
God I hate Access for this BS!  
Well, most troubles result from bad design, bad practices, low VBA knowledge ect ...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.