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.
5  3079  nico5038 3,080
Recognized Expert Specialist
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)
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 3,080
Recognized Expert Specialist
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)
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 3,080
Recognized Expert Specialist
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)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Larry R Harrison Jr |
last post by:
I have designed databases but have never come across any complications due
to the ridiculous situation of a hyphenated last name. As a database
designer (very junior level) I disdain anything unwieldly, and consider this
unwieldly.
I haven't dealt with it, as I said, but I figure if I do, I'd simply design
the text field to not accept hyphens, so Smith-Barney would be stored as
SmithBarney, or even Smithbarney as I commonly have the...
|
by: pcb |
last post by:
Hi All,
I have a procedure that concatenates sample numbers. The format of the
numbers are B-00001, B-00002, etc.
For example, if I have 3 sample numbers and I want to concatenate them
as follows:
('B-00001', 'B-0002', 'B-00003')
I wrote the following code:
|
by: Juan |
last post by:
Does any one know if there are reported bugs when concatenating strings?
When debugging each variable has the correct value but when I try to
concatenate them some values are missing (I can´t see them in the debugger).
After encoding the string (the sameone which complete value is not visible
from the debugger) all the values can be seen but they are spaced by big
amounts of zeros and use more that the 2048 bytes allocated. It is like if...
|
by: FB's .NET Dev PC |
last post by:
Interesting note, the code below as is will attempt to cast what is clearly
indicated as a string into a double. This is becuase the use of + as a
concatenation operator. The error message generated is included with the
code. Use & instead and it works.
Dim pstrSend As String = ""
'ActionCode 1 and 2 are type BYTE, Msg is a byVal string parameter
|
by: Antonio Tirado |
last post by:
Hi,
I'm using the OleDB namespace to open a CSV File. I can read the file
sucessfully except for hyphenated values. My CSV File contains phone numbers
and sometimes these come separated with hyphens (ie: 555-123-4567). These
fields come as empty using the OleDB namespace and as dbNull when using the
ODBC namespace. Is there a way to prevent this from happening?
Thanks in advance,
| | |
by: lindiwemaduna |
last post by:
I want to concatenate values of two text boxes into one string but these should be separated by a space in the database table. i have tried all the following but twas not successful:
Dim fullName as String
Dim Surname as String
Surname = String.Concat(txtSurname.Text, Chr(173)) --this one is meant to append an empty string to txtSurname)
FullName = String.Concat(Surname, txtName.Text) -- now I am concatenating tha above variable and...
|
by: Mary |
last post by:
I have a student who has a hyphenated first name. If I concatenate the
name like this: StudentName:( & ", " &
), it works as expected. If, however, I try to get
the first name first by concatenating like this: StudentName:
( & " " & ), only the first name
appears.
I sure would appreciate any help!
Mary
|
by: plumba |
last post by:
Hi all.
I have a form which has a table and within the rows of the table are simple text input boxes. The ONLY problems with this is that when the form is posted (in text format) if the number of characters in a text box goes over 30 the next text box is concatenated in the email which the form sends!!
The table with the boxes is here....
<form method="post" action="mailto:an.email@address.com?subject=EN$(FORMNO)"...
|
by: clinisbut |
last post by:
I'm not sure if this is the right group, but I didn't found any other
more appropiate to post my problem.
I'm trying to concatenate chars using the Glib library and I'm getting
strange behaviours.
This is my code:
int num_chars = strlen( text );
vector< gchar* sourceStrings;
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
