Type Mismatch Paramaterized Query ?!

[bev]

I'm working in Access 2000 on code that was developed years ago in Access 97. I'm getting a data type mismatch, but I'm having trouble working out what to do.

My VBA code contains the following code:

Set oQuery = CurrentDb.CreateQueryDef("", sSQL)
oQuery.Parameters!BeginFY = BeginFY

At this point I get the error msg:

Run-time error 3615: Type mismatch in expression

The second statement calls the following code:

Public Property Get BeginFY() As Date
BeginFY = DateValue(CDate(txtBeginFY))
End Property

where txtBeginFY is a date keyed in using the form: mmddyy and a mask: 99/99/00;0;_

The query object oQuery:
PARAMETERS BeginFY DateTime, EndFY DateTime;
INSERT INTO DestTableName ( Region, AgencyID, Satellite, Subcontract, SSN, ComponentCode, ServiceCode, ServiceDate, Units, Each, Total )
SELECT [tblRegionsSAPT].[fldRegion], [tblUPS03].[fldAGENCYID], [tblUPS03].[NewSatelliteCode], [tblRegionsSAPT].[fldSubcontract], [tblUPS03].[fldSSN], [tblUPS03].[fldOldComponent], [tblUPS03].[fldServiceCode], [tblUPS03].[fldServiceDate], [tblUPS03].[fldUnits], [tblUPS03].[fldCost], [tblUPS03].[fldTotalCost]
FROM tblUPS03 INNER JOIN tblRegionsSAPT ON ([tblUPS03].[fldAGENCYID]=[tblRegionsSAPT].[fldAgency]) AND ([tblUPS03].[fldSatellite]=[tblRegionsSAPT].[fldSatellite])
GROUP BY [tblRegionsSAPT].[fldRegion], [tblUPS03].[fldAGENCYID], [tblUPS03].[NewSatelliteCode], [tblRegionsSAPT].[fldSubcontract], [tblUPS03].[fldSSN], [tblUPS03].[fldOldComponent], [tblUPS03].[fldServiceCode], [tblUPS03].[fldServiceDate], [tblUPS03].[fldUnits], [tblUPS03].[fldCost], [tblUPS03].[fldTotalCost]
HAVING (((tblUPS03.fldServiceDate) Between [BeginFY] And [EndFY]));

From searching the web I know there's a problem with paramaterized dates, but I'm at my wit's end figuring out how to eliminate the mismatch.

Can any of you wonderful, kind wizards help?

Bev

 

[willir]

Bev

where txtBeginFY is a date keyed in using the form: mmddyy and a mask: 99/99/00;0;_

Instead of using a numeric mask (input mask), delete this and try using the Format - short date. (Form properties, Format tab)

Richard

 

[bev]

That didn't seem to work. I've done more investigating and it seems the parameters have not been set at all.

Specifically, though the SQL is as above, the value for: oQuery.Parameters!BeginFY
displays <type mismatch in expression> when I highlight it and pause my cursor over it. When I view the local variables, there do not appear to be any parameters.

Don't know if this is any help, but the db was originally Access97. It exceeded 2Gb and wouldn't allow me to open it with Access2000 (haven't been able to find a machine that still has Access97 on it). I created a new Access2000 db and imported the objects I thought I needed.

I eliminated some earlier problems by removing the reference to ActiveX and adding a reference to DAO 3.6, but I'm still having this particular problem. I now think it's not an issue of the dates being incompatible but somehow the parameters not being created ... or the query not being created.

Is there another reference I need to add for backward compatibility? This ran last year just fine and I haven't altered the code.

Or could somebody help me with 1) figuring out if the querydef exists, and/or 2) creating the parameters in the querydef? I know I don't have the syntax, but something like oQuery.Parameters.CreateParameter(1) = "[BeginFY]"

All help is welcome!
Bev

 

[willir]

Hi Bev

It exceeded 2Gb and wouldn't allow me to open it with Access2000

Ouch - you have maxed out!

http://www.databasezone.com/techdocs/acclimit.html

...and Access 2000 handles more data / larger database size than Access 97.

Have you run the Compact and Repair? (from the Menu, "Tools" -> "Database Utilities") (And I am sure you know th three rules of IT - backup, backup, backup.)

As per your syntax...[tt]
Public Property Get BeginFY() As Date
BeginFY = DateValue(CDate(txtBeginFY))
End Property
[/tt]

I guess why use DateValue + CDate?

DateValue: Returns a Variant (Date). ... The required date argument is normally a string expression

cDate: Return Type: date

Try using just CDate.

 

[bev]

Folks,

I'm so very embarrassed! It turns out that when I updated the query to use tblUPS03 (it had previously used tblUPS02), I broke the query and didn't realize it! MEA CULPA!

So the issue wasn't a coding error at all; it was a linking error in the above referenced query.

Thanks so much for your help. Sorry if I wasted anybody's time.

Bev

 

[willir]

Bev
Glad it was so easy.

As stated before, your database, if about 2 GB in size, is approaching size limitations. You may want to plan accordingly.

Richard