Append Query

[RandyH]

I'm trying to run an Append Query where
Records from [Style].[MatchString] are Appended to [New Billing].[New Style] with no duplicates in [New Billing].[New Style]

INSERT INTO [New Billing] ( [New Style] )


SELECT [New Billing].[New Style]
FROM [New Billing] RIGHT JOIN Style ON [New Billing].[New Style] = Style.MatchString
WHERE ((([New Billing].[New Style]) Is Null) AND ((Style.MatchString) Is Not Null));


When I use the View Button it shows (doesn't actually show results), but does display the correct destination [New Billing].[New Style] and indicates 52,000+ records. When a run the Query I get this message. And 0 records are appended

Job Tracking (name of my Program) can't append all the records in the append Query.
Job Tracking set 0 fields to Null due to type conversion failure and didn't add 0 records...


[New Billing].[New Style] is indexed, no duplicates. I have other Append Queries that appends what records are permitted and not the restricted ones. Although those Queries only handle a few hundred records at a time.

Any Suggestions?

[Micron]

That message is generic and always includes all reasons why. Those with 0's don't apply (like the Null one). Those with other numbers apply so which condition applied for you?
Or you can run this query in code and suppress warnings and it should append what records it can and bypass what it can't. You should code it so that if it errors you turn warnings back on with an error handler.

The datasheet view of an action query will at least show the number of records to be affected (depending on what you're doing) so you could scroll down to see the record count if that will give you some clue. In your case that might be thousands of records though. Pretty sure your issue has nothing to do with the record count. The missing clue is which of the 4 (or is it 5?) parts of the message applies here. Could even be more than one reason.

[RandyH]

Thanks for your response. Not sure what you mean.
" Or you can run this query in code and suppress warnings and it should append what records it can and bypass what it can't. You should code it so that if it errors you turn warnings back on with an error handler."

[Micron]

Something like the following 'air code'

Sub RunQuery()
Dim sSql As String
Dim db As DAO.Database

On Error GoTo errHandler

Set db = CurrentDb
sSql = "build/write action query sql statement here"
Docmd.SetWarnings False
db.Execute sSql, dbFailOnError

exitHere:
DoCmd.SetWarnings True
Set db = Nothing
Exit Sub

errHandler:
Msgbox "Error " & err.Number & ": " & err.Description
Resume exitHere

End Sub

If your query raises an error and warnings were turned off, the error handler presents the message and control passes to the exitHere line label where warnings are turned back on. The message you got is a warning and setting SetWarnings to False should disable it. If you need to know how many records were appended, I believe the RecordsAffected property of the Execute method (db.Execute) will report updated as well as appended. Maybe verify that if you're concerned. Otherwise, the query sql should run and append 0 records or thousands but you wouldn't get a direct indication.

[orange]

See this info re Insert into for samples.
With Access it is better to not have embedded spaces in field or object names.

Can you describe in plain English what you are trying to achieve?
As requested in a previous post, could you tell us about the styles to help put your posts and database into context?

[RandyH]

My previous posts about style seem to have been solved with a modified Module written by John. I still testing that module and when convinced I will mark the post as solved.

Appending the [Style].[MatchSting] to [New Billing].[New Style] is part of that process.

In this post did you see any reason why my Append Query (above) is not working as expected?

Thanks Orange!

[RandyH]

Going through the material you recommend it appears Access will only append records that have the same Column Name in both the Source and Destination Tables. And resists Appends when both the Source and Destination Tables have Columns that are the same name.

I want to append [Style].[MatchString] to [New Billing].[New Style] even thought Tbl [New Billing] has a column [Matchstring]

Please help!!!

[Micron]

Please help!!!

You can help others help to help you by answering the questions asked of you.

Those with other numbers apply so which condition applied for you?
.
The missing clue is which of the 4 (or is it 5?) parts of the message applies here. Could even be more than one reason.

I don't see the relevance of that link so I'll leave it to Orange to answer your questions re that. You can append **any value from any table from any field to any field in any table** regardless of names so I don't understand those comments.

**subject to any constraints imposed by design - e.g. data types, field size, required or not, etc.

[RandyH]

Sorry, but what questions have I not answered?

Sincerely,
RandyH

[Micron]

I just quoted them for you? Go back to my first post and you'll see I quoted from it.
If you answered them I missed it but I've looked more than once.

[orange]

We need to see your structure-- as requested.

Can you describe in plain English what you are trying to achieve?
As requested in a previous post, could you tell us about the styles to help put your posts and database into context?

[RandyH]

Changed Validation Rules and got it to work. Thanks