Data Type Mismatch

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
Hey there ....having issues passin a string in my parameter qry.

here is the code
Code:
Dim rsAgntAdd As Recordset
Dim strAgntAdd As String

Dim fldsAgnt As DAO.Fields
Dim fldAgnt As DAO.Field

Set rsAgntAdd = CurrentDb.OpenRecordset("zzzz_qry_MaxDateParmDate")

Set fldsAgnt = rsAgntAdd.Fields
Set fldAgnt = fldsAgnt("ParmDate")

If rsAgntAdd.RecordCount > 0 Then

With rsAgntAdd
    .MoveFirst

Do While Not .EOF
    strAgntAdd = fldAgnt

Const QRY_APPEND = "zzz_qry_TESTINGPASS"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
'DoCmd.SetWarnings True
Set db = CurrentDb
'this is the append query which ontain the parameter to gether necessary info
Set qd = db.QueryDefs("zzz_qry_TESTINGPASS")
qd.Parameters(0) = strAgntAdd
'runs the append query to build a running log of data to a defined table
qd.Execute

the issus is my strAgntAdd is a date filed and i'm trying to string a between date adn date.

so, if i hover over my strAgntAdd as i'm stepping thru the code it shows "Between 5/1/13 And 5/12/13"
but i get a data type mismatch......so i tried altering the string (from the ParmDate field to be "Between #5/1/13# And #5/12/13#"....same error.

here is the formula for ParmDate:
ParmDate: "Between #" & Format([MaxOfrow_date]+1,"mm/dd/yyyy") & "# And #" & Format(Now()-1,"mm/dd/yyyy") & "#"

any ideas?
thanks
tuk
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Can you post the SQL for the append query?
 
Upvote 0
Code:
INSERT INTO tbl_TESTING_2 ( row_date, split, SumOfacdcalls )
SELECT root_dsplitCO.row_date, root_dsplitCO.split, Sum(root_dsplitCO.acdcalls) AS SumOfacdcalls
FROM root_dsplitCO
GROUP BY root_dsplitCO.row_date, root_dsplitCO.split
HAVING (((root_dsplitCO.row_date)=[parameter]));

i think i'm running into issues because my strAgntAdd has quotes around it.....total guess.
 
Last edited:
Upvote 0
I think that might be a problem.

I would have expected to see something like this for the criteria,

HAVING root_dsplitCO.row_date Between [parameter]+1 And Now()-1

and you would use a date for the parameter.

The way you have it is as though you are comparing root_dsplitCO.row_date, which I assume is a date/time field, to the string "Between #" & Format([MaxOfrow_date]+1,"mm/dd/yyyy") & "# And #" & Format(Now()-1,"mm/dd/yyyy") & "#".

That might explain the type mismatch in the code.

What happens when you run the code manually?
 
Upvote 0
I wouldn't write a parameter as a BETWEEN clause. Write it as two dates. The query should read:
Code:
INSERT INTO tbl_TESTING_2 ( row_date, split, SumOfacdcalls )
SELECT root_dsplitCO.row_date, root_dsplitCO.split, Sum(root_dsplitCO.acdcalls) AS SumOfacdcalls
FROM root_dsplitCO
GROUP BY root_dsplitCO.row_date, root_dsplitCO.split
HAVING root_dsplitCO.row_date BETWEEN ([StartDate] AND [EndDate]);

Then when you run the query you set each of the *two* parameters and you're done. When you set the parameters, give them a real date data type (not a string). Also I find it useful to always explicitly declare parameters - use the parameters menu item in query design to name them (the same ones as in your query) and give them data types. By doing this, you provide a "hint" to Access as to what it's getting, and it also validates the input to make sure it is the correct data type.
 
Last edited:
Upvote 0
hmm I'm still having problems with this?

so are you saying to use the query def concept or a docmd.runsql? my preference would be a qry def.

can you please elaborate on how i would go about setting the *two* parameters? in the design mode of the qry what do i need in the criteria line for row_date?

thanks
 
Upvote 0
alright.....i figured it out. thanks for you help. for reference:

im my query i have the criteria line as ">[StartDate] And <[EndDate]"

here is the code:

Code:
Private Sub ParameterQry()
DoCmd.SetWarnings False
Dim rs As DAO.Recordset

Dim rsAgntAdd As Recordset
Dim strAgntAdd As String

Dim fldsAgnt As DAO.Fields
Dim fldAgnt As DAO.Field

Dim fldsEnddate As DAO.Fields
Dim fldEnddate As DAO.Field

Set rsAgntAdd = CurrentDb.OpenRecordset("zzzz_qry_MaxDateParmDate")

Set fldsAgnt = rsAgntAdd.Fields
Set fldAgnt = fldsAgnt("StartDate")

Set fldsEnddate = rsAgntAdd.Fields
Set fldEnddate = fldsEnddate("EndDate")



If rsAgntAdd.RecordCount > 0 Then

With rsAgntAdd
    .MoveFirst

Do While Not .EOF
       Set StartDate = fldAgnt
       Set EndDate = fldEnddate


Const QRY_APPEND = "zzz_qry_TESTINGPASS"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
DoCmd.SetWarnings True
Set db = CurrentDb
'this is the append query which ontain the parameter to gether necessary info
Set qd = db.QueryDefs("zzz_qry_TESTINGPASS")

qd.Parameters(0) = StartDate
qd.Parameters(1) = EndDate

'runs the append query to build a running log of data to a defined table

qd.Execute

    rsAgntAdd.MoveNext

Loop
End With

Else
End If

Set rsAgntAdd = Nothing
Set fldsAgnt = Nothing
Set fldAgnt = Nothing

Set fldsEnddate = Nothing
Set fldEnddate = Nothing

Set StartDate = Nothing
Set EndDate = Nothing

DoCmd.SetWarnings True

End Sub
 
Upvote 0
im my query i have the criteria line as ">[StartDate] And <[EndDate]"

Just a note that if you wanted to have the same result as BETWEEN then greater than or equal to and less than or equal to are the equivalents:
>=[StartDate] And <=[EndDate]

Just check yourself and the results to make sure its right.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top