Append Query WHERE clause - Wat.

[The Archn00b]

Hello! I have a problem that I hope will be easily solved.

I have an Append query that copies records from a table "Sightings" in one accdb file to a table "Observations" in another .accdb file.

The Sightings table has the following fields:

• ID - Autonumber
• Species_ID - Number
• DD - Number
• MM - Number
• YYYY - Number
• Location - Text
• Recorder_ID - Number
• Number_Sighted - Number
• Verified - Yes/No
• Appended - Yes/No

All data within all fields are to be Appended to the table "Observations" EXCEPT "Verified" and "Appended."

The problem arises in getting only data which has a "True" value in the verified field Appended if the Query is run.

The SQL looks like this:

INSERT INTO Observations ( Species_ID, DD, MM, YYYY, Location, Number_Sighted, Comments, Recorder_ID ) IN 'F:\ARC\Observations_BE.accdb'
SELECT Sightings.Species_ID, Sightings.DD, Sightings.MM, Sightings.YYYY, Sightings.Location, Sightings.Number_Sighted, Sightings.Comments, Sightings.Recorder_ID
FROM Sightings
WHERE Sightings.Verified='True';

However when I run it, we get "Data Type Mismatch in the Criteria Expression"

It must be something really simple, because I thought this shouldn't be too difficult of an operation. But if anyone could correct my SQL code that would be a massive help.

Thanks for reading!

 

[pbaldy]

Either

WHERE Sightings.Verified=True

or

WHERE Sightings.Verified=-1

 

[The Archn00b]

Either

WHERE Sightings.Verified=True

or

WHERE Sightings.Verified=-1

Thank you that worked. Thanks for your time.

 

[pbaldy]

Happy to help, and welcome to the site by the way!

 

[The Archn00b]

Many thanks, I appreciate that. Rather than start another thread, I also have another question please. Just Yes or No will do. Can you have multiple SQL statements within a single Access Query? Can I make a query that at once Appends rows to another table and then updates rows in the source table?

 

[pbaldy]

In Access, no.