Type mismatch in expression problems

[Rik_StHelens]

Hi

I have a database which relies on importing data from .dbf files generated from a DOS system

The import has worked fine until today and I am now recieving a type mismatch in expression error. I have not made any changes to the database, and it was working the last time data was imported

I have manually checked the data types of all fields which showed up nothing, and rebuilt the query field by field, although I got the type mismatch error with the first field i entered into the query


Im completely stuck for ideas....

Any suggestions?

 

[MStef]

May be:
If you import a numeric field, but in this field is nothing (empty).
It can cause this error.

 

[vbaInet]

Are you using a function to perform your import routine?

 

[Rik_StHelens]

Thanks for your replies

A) I am only importing 1 numeric field, and this would never be blank because it is from a static table with contact details in it.

B) I am not using any routine or code to automate the process. The user clicks an import button, and it runs a series of appends and update queries to filter out duplicates and assign values to certain fields.

 

[gemma-the-husky]

what are you importing to?

examine the intial import file, before you run the queries.
run the queries manually.

there must be something, and it shouldnt be too hard to find.

 

[Rik_StHelens]

what are you importing to?

try importing to a temp file, and see what data types you get.

The dbf files are all linked tables. All the query does is append the necessary fields out of these unmodifiable dbf files..... and then add in some other data based upon the appended fields values.... into an Access 2007 table


Probably a daft question, but how do you import to a temp...?

 

[gemma-the-husky]

manually

file/external file/import answer the questions

different versions of access are slightly different - but the wizard will be on the file menu.

 

[DCrake]

If you open up your linked dbf files do you see anything out of the ordinary in the early rows?

Try running a make table on these tables and see how Access defines the data types. Does it recognise any numeric fields as being text or are they any missing data items.

David

 

[Rik_StHelens]

If you open up your linked dbf files do you see anything out of the ordinary in the early rows?

Try running a make table on these tables and see how Access defines the data types. Does it recognise any numeric fields as being text or are they any missing data items.

David

the latest few transactions are credits back to customers, rather than standard transactions

maybe this is why its throwing back an error?

i'll have to check again next week after we've run an end of day on our current system

we are also migranting to an sql database but our 3rd party developers dont think it is an issue as it is just mirroring data at the minute from the dbf files

 

[gemma-the-husky]

the latest few transactions are credits back to customers, rather than standard transactions

maybe this is why its throwing back an error?

i'll have to check again next week after we've run an end of day on our current system

we are also migranting to an sql database but our 3rd party developers dont think it is an issue as it is just mirroring data at the minute from the dbf files

maybe the credits have a text field in a certain place, where invoices have a numeric field - that might be enough to make the difference

ALMOST CERTAINLY this error is caused by a field you are expecting to be numeric appearing as a text. That is what you are looking for, I am sure

 

[Rik_StHelens]

Thanks again for your reply.

After thinking about it, it could also cause problems with the relationships which are used to pull in other details

I am still waiting for the files to be updated (we've got a lot going on with a new sql system so its low priority...)

i will let you know what happens

 

[Rik_StHelens]

I have finally had a chance to look at this as our records have been updated (it used to be daily but we are having a new system put in place which has affected things)

The query still returns the type mismatch in expression error

I have limited the date parameter on the query so that it only looks at records which were processed AFTER the credit was issued, and this still made no difference...

damn.

 

[vbaInet]

Maybe you could try casting all your values to the data types they are set as in your table. For example, CInt([Field]) will cast your data to an Int type. It could also be that you have Null values in a numeric field which it can't interpret. Have you checked for those?

 

[Rik_StHelens]

i'll give it a whirl and see what comes back

thanks for the help!

 

[Rik_StHelens]

I now know exactly what is causing the problem.

When ever we get a breakdown, we create a jobsheet which is later turned into an invoice.

All jobsheets and invoices have a matching unique ID, known as an FI Number.

When the query runs, it looks at the FI number which joins the invoice & job tables, and pulls in wheel positions, tyre sizes etc.

When an account credit is issued to refund a breakdown gone wrong, it creates an FR Number in the invoice table, which therefore cannot link to the corresponding FI number on the job record, causing the mismatch error on the join

I have tried excluding all records which begin with FR, but it still returns the error. Here is the full query SQL:

INSERT INTO [Historical Casing Jobs] ( CUSTOMER, INVNUM, DELIVERY, INVDATE, REGNUM, [POSITION], TREADM, SERIALON, SERIALOFF, [SECTION], PROFILE, RIM, [SIZE], [New Or Rems], STCODE, Brand, [Removal Description], DESCRIPN, [Post Code], OLDADVNUM, [Retreader Agent], SUMCODE, NAME1, VAN, Destination, [Pos No] )
SELECT TRNLIST.CUSTOMER, TRNLIST.INVNUM, TRNLIST.DELIVERY, TRNLIST.INVDATE, TRNLIST.REGNUM, Positions.[Position Name], TRANSFI.TREADM, TRANSFI.SERIALON, TRANSFI.SERIALOFF, STOCK.SECTION, STOCK.PROFILE, STOCK.RIM, [SECTION] & "/" & [PROFILE] & "R" & [RIM] AS [SIZE], [Tbl New Rems Code].[New or Rems], TRANSFI.STCODE, [Tbl Tyre Manufacturers].Brand, [Tbl Removal Codes].[Removal Description], VANRUN.DESCRIPN, VANRUN.[Post Code], TRNLIST.OLDADVNUM, [Tbl Retreader-Dealer Assoc].[Retreader Agent], STOCK.SUMCODE, TRNLIST.NAME1, TRNLIST.VAN, Destination.Destination, TRANSFI.POSITION
FROM Positions INNER JOIN (Destination INNER JOIN ((((((((TRNLIST INNER JOIN TRANSFI ON TRNLIST.INVNUM = TRANSFI.DOCUMENT) LEFT JOIN STOCK ON TRANSFI.STCODE = STOCK.STCODE) LEFT JOIN [Tbl Removal Codes] ON TRANSFI.REASON = [Tbl Removal Codes].[Removal Code]) LEFT JOIN [Tbl Tyre Manufacturers] ON TRANSFI.BRAND = [Tbl Tyre Manufacturers].Code) LEFT JOIN [Tbl New Rems Code] ON TRANSFI.REMOULD = [Tbl New Rems Code].[New Rems Code]) LEFT JOIN VANRUN ON TRNLIST.VAN = VANRUN.NUMBER) INNER JOIN [Tbl Retreader-Dealer Assoc] ON TRNLIST.VAN = [Tbl Retreader-Dealer Assoc].[VANRUN Number]) INNER JOIN [Tbl Customer Retreader Association] ON (TRNLIST.CUSTOMER = [Tbl Customer Retreader Association].[Customer Code]) AND ([Tbl Retreader-Dealer Assoc].Retreader = [Tbl Customer Retreader Association].[Retreader Agent])) ON Destination.[Dest No] = TRANSFI.CASDEST) ON Positions.[Position No] = TRANSFI.POSITION
WHERE (((TRNLIST.INVNUM) Not Like '%FR%') AND ((TRNLIST.INVDATE)>=Date()-45) AND ((Positions.[Position Name]) Is Not Null) AND (([SECTION] & "/" & [PROFILE] & "R" & [RIM])<>"/R"))
ORDER BY TRNLIST.INVNUM;


How can i exclude all records which have an FR ID so that access does not attempt to make the join and throw an error back at the user?

Thanks for your help & getting me this far!!