Concatenating hyphenated numbers with text

[rajsa]

Using a setvalue macro, I'm trying to concatenate several fields, all of which are set as 'text', but some of which contain only numbers. (Gathering data from a form into one field, then saving this back to the table)
I have got around the problem by using CStr(), but this isn't working for one field, where I have two numbers (page numbers) seperated with a hyphen, e.g. 378-392. If I leave out the Cstr(), I get a Type Mismatch error, if I include it, I get a concatenated result but with Error 0 at the place where this field value should be.

I'm using Access 2002, and am not very experienced, as may be obvious.

Any help appreciated.

[nico5038]

Hmm, strange.
When you concatenate the fields, are you using the "&" or the "+" sign ?
Can you post the code you have ?

In general when concatenating fields and having one text field, will turn the entire concatenation into a textstring concatenation....

Nic;o)

[rajsa]

Here goes:

I have a form in which the user enters the various parts of journal citations, e.g. Authors, Year etc. What I want is for one field to display the whole citation, by concatenating these fields, and then save that concatenated value to a field in my table.

In my setvalue macro I have the following:
Item: [Reference]
Expression:

[Authors] & " (" & CStr([Year]) & ") " & [Title] & " " & [Publication] & " " & CStr([Volume]) & ": " & CStr([Pages]) & "."

As I've said, when I delete the last section (Pages), it works fine, but as soon as I use the full code above, whether I use the final CStr() or not, I get errors. I'm assuming this is because Access sees the hyphen in the Pages field (e.g. 378-402) as a minus sign and therefore doesn't know what to do with it??

All fields are set as text, however [Year], [Volume] and [Pages] contain only numbers. I found that using CStr() worked for the first two (got rid of my type mismatch) by trial and error.

Thanks!

[nico5038]

Hmm, looks like the syntax is OK, but when you would use this on a report the [Pages] would give you the number of report pages, instead of the desired value from your table. Could you try to change the fieldname "Pages" in the table into "BookPages" and rebuild the string to use that field instead ?

Nic;o)

[rajsa]

Thanks - I'll change the field name, I hadn't thought of that (would have probably flummoxed me when I came to producing a report!)

I ditched the setvalue macro idea for now - pasting the same expression (without the CStr() functions) into an update query worked perfectly, and I've just added a button to the form so whenever new references are added, the query can be run.

[nico5038]

Glad I could help. The "reserved words" (like Pages, etc.) are indeed a nasty problem to detect. "Qualifying" fieldnames (like BookPages and DateSold) does often help.

Success with your application !

Nic;o)