Filling in extra fields in an append query

[domino3]

I have an append query which takes a specific record from an existing table and adds just that record to a new table.

I would like to add three extra fields to the query which aren't on the original table but are required on the new table. This information would be the same for all new records appended in this way.

Is there any way of adding these to the append query so that they are added automatically when the query is run without the operator having to type them in manually?

 

[lupins46]

just add extra columns to your append query grid.

extra1:12345
extra2:"abcde"
and so on.

Then set the Append To fieldname using the drop down.

 

[domino3]

Thanks. That works fine, except for yes/no fields. I've tried putting in 1, 0, -1, yes and no, and none of those brings up yes or no in the fields. All I get is 0 or 1.

Is there any way of making the field fill correctly with yes or no?

 

[domino3]

I've solved it. Thanks for your help with the first question.

 

[lupins46]

Use the words True or False (No quotes)

 

[lupins46]

Sorry - withdraw that response - I realise I don't understand the problem you described.

 

[domino3]

Sorry, I hadn't solved the problem after all.

What I have is a field in my new table which is a yes/no field.

In the append query I have a parameter which says:

Expr6: [Attended previously - Yes/No]

This appears in a dialogue box to which the answer should be yes or no. However, I get a field violation when I try to run it.

Is there any way of solving this?

 

[lupins46]

Define the parameter as a yes/no datatype in Query>Parameters menu. Make sure you enter exactly the same text for the parameter name as you have in the gtid:
[Attended previously - Yes/No]

 

[domino3]

That works beautifully. Thank you.

 

[domino3]

Sorry. One further question.

I have a date field in my append query which I need to fill. Is there any way of setting the format for the dialogue box so that I only need to type in the numbers in the date, and not the forward slashes as well? So that all I have to type in, for example, is 010105, and the system will automatically convert it to 01/01/05. At the moment I am having to type in the forward slashes as well, which is OK until someone else enters data, and then can't work out why the record won't append.

Thank you.

 

[lupins46]

If it is defined as a date field Access will not let you type a string of numbers into it without giving an error.
It has to be in a format Access can recognise as a date.