Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

make table query - numeric field overflow 1

Status
Not open for further replies.

tractorvix

Technical User
Jun 29, 2004
122
GB
Does anyone know how to stop a make table query erroring with a numeric field overflow. I assumed that as it was a 'make' table query it would set the field sizes appropriately. it runs fine the first time round, but when I try to run it again, I get the error.

I've also tried using an append query to do this and get the same error.

code below
SELECT InStr(Mid([RMS Data]![Staff Name],3,6),"-") AS rmsidbuild, Left([RMS Data]![Staff Name],(1+[rmsidbuild])) AS RMSID, [RMS Data].[Career Path] AS RoleID, [RMS Data].[Project Cost Centre] AS CC, [RMS Data].Month, [RMS Data].[HOIT Name] AS Area, [RMS Data].Direct, [RMS Data].[Project Owner] AS LineMgr, [RMS Data].Demand, InStr([RMS Data]![Developer Sub Group]," Developers") AS skillbuild, IIf([RMS Data]![Developer Sub Group] Is Null,"",Left([RMS Data]![Developer Sub Group],[skillbuild])) AS Skill, [RMS Data].Vacancy, [RMS Data].[Capability Res Type] INTO tblbuildRMS
FROM [RMS Data]
WHERE ((([RMS Data].Vacancy)="true") AND (([RMS Data].[Capability Res Type]) Like "1*") AND (([RMS Data].[Project Title]) Not Like "zz*"));

Thanks
Vix

 
that's not a make table query...

basically:
first time, you are inserting into a non-exsistent table, so the engine automatically creates a table, with appropriate fieldtypes and sizes, and insert the records...

2nd time, the table already exsists so it will automatically insert into that table. However if you have a value which is too big for a field then you'll get an error.

if you want to do something like this as an automated process, then either do a make table query beforehand and set proper field types/sizes, and then insert or you need to drop the table each time before you insert...

--------------------
Procrastinate Now!
 
Crowley16

Sorry ... but no. The query is a "Make Table" because of the INTO tblbuildRMS clause.

And it doesn't work as you describe.

If you run the query from the query pane in Access then it will create the table on the first run. If you run it again then Access will ask if you want to delete the current table before creating a new one.

If you run the query from code

(i.e. CurrentDB.Execute ...The Above SQL...)

then the table will be created on the first run and will raise a "Table Already Exists" error if you run it again.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
 
in that case you should check your data to see if there's any inconsisten entries...

--------------------
Procrastinate Now!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top