Append query - Problem with data type (maybe?)

[Big Pat]

Hi,

Not sure whether to post under queries, tables, or VBA but here goes:

Each week I receive four excel files (relating to different years' data) from another office and I save them into specific locations/filenames. My Access2003 db uses those four spreadsheets as linked tables. Each file is identical in format and my next step is to delete the previous week's data and then append each file into my "combined years" table.

I'm doing this as follows from a command button. This is just one of the statements, but the others are the same apart from the year in the filename.

Only the Study Recruit ID field is a problem!

DoCmd.RunSQL "INSERT INTO [Input file - combined years] ( [Topic ID], [Study ID], Acronym, [Study Title], [Active Status], Randomised, [Design Type], Phase, [Sample Size], [Accrual Site ID], [COLOR="Red"][Study Recruit ID][/COLOR], [Study Entry Date], Topic, [Topic Category], [Organisation ID], CLRN, TrustID, [Trust Name], [Site Name], [Investigator Name], [Count] )" _
            & "SELECT [2008-09 input file].[Topic ID], [2008-09 input file].[Study ID], [2008-09 input file].Acronym, [2008-09 input file].[Study Title], [2008-09 input file].[Active Status], [2008-09 input file].Randomised, [2008-09 input file].[Design Type], [2008-09 input file].Phase, [2008-09 input file].[Sample Size], [2008-09 input file].[Accrual Site ID], [2008-09 input file].[COLOR="red"][Study Recruit ID][/COLOR], [2008-09 input file].[Study Entry Date], [2008-09 input file].Topic, [2008-09 input file].[Topic Category], [2008-09 input file].[Organisation ID], [2008-09 input file].CLRN, [2008-09 input file].TrustID, [2008-09 input file].[Trust Name], [2008-09 input file].[Site Name], [2008-09 input file].[Investigator Name], [2008-09 input file].[Count]" _
            & "FROM [2008-09 input file];"

I know the field names aren't so good but that's how they come!

Study Recruit ID can contain text or numbers. It can be as simple as 3, or as complex as DCF40AE0-CF9F-DF11-8FF6-002264A1A658. So I have ensured that the data type of the target table is Text.

When I append the data, I get lots of blanks in the Study Recruit ID field and it appears the missing ones are those values that were numeric in the source tables. But shouldn't numeric values append OK into a text-field? I thought they did.

I've checked the design view of the linked tables and the field is text there too.

Fields with dates are appending OK, as are fields that contain just text or just numbers. It's just this one that can be either that's bugging me!

Any ideas how I should fix this?

Thank you,

 

[Guus2005]

in excel some numbers are considered numbers and some are text.
if you convert the column to text, it should work.

there is something you can do in your query to make sure the data is considered as tewxt:

cstr(nz([2008-09 input file].[Study Recruit ID],"")

when the field contains NULL, it is converted to "" using the Nz function. if it is a number it is converted to a string CStr().

HTH

 

[Big Pat]

Thanks so much - I was beginning to fear I'd get no replies to this one.

I don't want to interfere with the excel files (someone else uses them for a separate task) so I was hoping for an Access solution.

I'm off work at the moment and won't have access to that data for another couple of weeks. But your CStr(nz(... )) idea sounds like it'll work. I'm not all that familiar with functions like these, but would I be right to assume it should have a second closing bracket?

As an aside, am I wrong in my assumption that numeric data can be appended to a text-field and Access would treat it as text from that point on? I wonder where I got that idea.

Thanks again!

 

[Guus2005]

you're right about the second closing parenthesis.

As an aside, am I wrong in my assumption that numeric data can be appended to a text-field and Access would treat it as text from that point on? I wonder where I got that idea.

you are right. it is treated as text but you can perform any calculation on the result because the conversion to a number will be done implicitly if it is possible. if it is not possible, you'll receive an errormessage.

Share & Enjoy!