Adding a field prefix to appear in query results only (1 Viewer)

[adyas]

I have produced a query that includes a contatenated field as follows:

TextDescription: [EuropaPOno] & ", " & [conceptno] & ", " & [servicescheduleddate] & ", " & [site] & ", " & [suppliername]

This query will be run on a monthly basis and currently the following text is being added as a prefix to this field manually in Excel:

AccP112

This text refers to accruals for period 1 year 2012 and will change each month i.e. AccP212, AccP312

Is there anyway that this text can be added as a prefix to the concatenated field at the point of running the query, i.e. is it possible for the query to ask if any text is to be included as a prefix to this field?

This prefix text does not appear in the database at all and does not need to be saved once the query is run and the data is dropped into an excel form for issue to another department.

All guidance is much appreciated.

Allison

 

[pbaldy]

Try this to let the query ask:

TextDescription: [Enter prefix] & [EuropaPOno] & ", " & [conceptno] & ", " & [servicescheduleddate] & ", " & [site] & ", " & [suppliername]

to hard code it:

TextDescription: "Accp112" & [EuropaPOno] & ", " & [conceptno] & ", " & [servicescheduleddate] & ", " & [site] & ", " & [suppliername]

 

[adyas]

This works perfectly; very simple too.

Just for clarification, can I ask if the the word 'Enter' is the key to this solution?

I appreciate your assistance.

Very kind regards

Allison

 

[plog]

The key to that solution isn't "Enter". [Enter prefix] could be anything (i.e. [BlahBlahBlah], [PlogRules], [EtcEtc]) as long as whatever you put in there isn't a field available to the query.

Another solution, is to calculate what the numbers after "Accp" need to be--this way you can run the query and it automatically knows what to put after "Accp". When do you run this query--in the same month that needs to be referenced in the Accp or the month after? Or is it possible that you have a date field that determines this?

If you run the query in the same month then the below would work:

TextDescription: "Accp" & Month(Date()) & (Year(Date()) MOD 100) & [EuropaPOno] & ", " & [conceptno] & ", " & [servicescheduleddate] & ", " & [site] & ", " & [suppliername]

 

[MSAccessRookie]

This works perfectly; very simple too.

Just for clarification, can I ask if the the word 'Enter' is the key to this solution?

I appreciate your assistance.

Very kind regards

Allison

The key to the solution is the fact that MS Access thinks that you entered a Field namd (text between the brackets) that is not defined. When you do that, MS Access stops and requests you to provide a value for the unknown field(s). The actual text between the brackets is of little importance, and is normally used to send a message to the operator of the program.