I am trying to create a basic query that adds four numbers, but when I try '[Mark1]+[Mark2]+[Mark3]+[Mark4]' it concatenates it instead of adding.
e.g. I have '58+96+13+45' and I get '58961345' instead of 212.
I have a firstname field, and a surname field.
On a report I have a textbox that is bound to =[firstname] & " " & [surname]
This gives me an error. I have even tried single quotes, but no luck.
Please advise
Thank you in advance
Hi All:
, I have two fields to concatenate.
SKU = ps1-
Supp_sku = 98756
I want to have it like ps1-98756.
I have used the following query to test the result -
select sku & “ “ & supp_sku as [my_sku] from inventory_master;
The result is
My_sku = 198756
I do not get ps1-98756.
Could you please help?
Thanks - Prabir
G'day,
I want to produce some UPCa bar codes which require 11 digits of TEXT for the barcode function to work.
I want the bar code to be made up of several fields
Make 3 Chars (Text) (eg 021)
Model 3 Chars (Text) (eg 103)
Serial_Number 5 Characters (Numeric) (eg 00025)
(I need to keep the Serial_Number field numeric so that it increments in modulo 10 format (ie get Numeric only results))
So i need to produce a field that looks like this 02110300025 after the 3 fields are concatenated
However I am getting 02110325
I'm sure that the answer is simple but I seem to be having a mental block. Does anyone have any ideas? Your help will be very appreciated.
Hi folks,
New person to the forums here. I've had a scroll through the FAQs and can't see an answer to a problem I need to solve. I wonder if there's anyone could offer some help?
My db has three tables: table_a, table_b and table_c. Each table has the following fields:
table_a
a_id (PK)
a
table_b
b_id (PK)
b
table_c
c_id (PK)
a_id (FK (many to one))
b_id (FK (many to one))
c
I would like field c in table c to be a concatenation [table_a].[a]+"-"+[table_b].[b]. I'd like this field to automatically generate and be constrained as unique. Creation of value c in table_c should be by selection of value a & then value b in a form based on table_c.
Can anyone advise me how I do this?
By the way - editing to add this - I am using MS Access 2000 & can't upgrade to a later version.
I am using this as a criteria for a Query that is pulling the 2 dates from a form - Report Date Range.
The reason I am trying to do this is I have a cumulative report that needs to run for the fiscal year (1/1/xx to 12/31/xx). It will always use 1/1/xx as the base date for the range. I am trying to pick up the year because it will change depending on the periods/years picked.
Between #1/1/ & Year ([Forms]![frmReportDateRange]![txtBeginningDate]) & # And [Forms]![frmReportDateRange]![txtEndDate]
When I run the query I get "The expression you entered has an invalid date value."
I am trying to concatenate the 1/1/ with the year and obviously that is not working. Any ideas?
Thanks in advance!
I'm trying to concatenate several fields from a set of tables/subtables. But for some reason, it concatenates the first 3 records, then stops. I should clarify that the concatenation "skips" a subtable. Here's the structure of the relationships (not sure if this pic is working so I'm adding the link to the screen shot)
[URL] ....
and the concatenation itself
As you can see, I'm looking to create a concatenation of (Vendor_Code)&(Vendor_Item_Code)&(Item_ID) which runs through the subtable Items_Child, but doesn't use any field in Items_Child
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.
So for example
Column1 Column 2 Column 3
B11 Europe
would concatenate as:
B11.Europe.
Hi all,
Ive got a table called tblClientCar, this is its structure:
ClientCarID
ClientID
CarID
I want to use an append query to concatenate ClientID and CarID together and putting the concatenated value into ClientCarID. How would i do this?
Ive tried using a form a two combos so when i select the clientID from the 1st combo and then the CarID from the 2nd combo, then run the append query to write the concatenated value into ClientCarID of the table. But it says writing 0 rows to table and the ClientCarID isnt concatenated when i check the table. This is the query sql syntax i was using:
INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID )
SELECT tblClientCar.ClientCarID, tblClientCar.ClientID, tblClientCar.CarID
FROM tblClientCar
WHERE (((tblClientCar.ClientCarID)=[Forms]![frmClientCar].[Combo6] & "" & [Forms]![frmClientCar].[Combo8]));
I also tried another way. The 2 combos add the selected values into ClientID and CarID in the table tblCar. So i made the append query slightly different to concatenate ClientID and CarID to form ClientCarID. This is my query sql syntax:
INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID )
SELECT tblClientCar.ClientCarID, tblClientCar.ClientID, tblClientCar.CarID
FROM tblClientCar
WHERE (((tblClientCar.ClientCarID)=[ClientID] & "" & [CarID]));
I get the same result as the 1st query. What am i doing wrong?
Is there another way of doing what im trying to do? Whats the correct sql syntax for doing this?
Thanks
Jim
We use two different systems in our office that have notebook facilities. One of them stores only a limited number of characters in the note field per record and as such, some notes actually span several records.
The other system stores the whole note in one record.
We want to transfer all the notes from the first system (1 note over several records) to the second system (1 whole note in 1 record).
I have a table with the notes from the first sytem which looks something like
Note1 - Line1 - Text
Note1 - Line2 - Text
Note2 - Line1 - Text
Note3 - Line1 - Text
Note3 - Line2 - Text
Note3 - Line3 - Text
What I want to do is, for each note, combine the separate lines into one record.
I have been able to do this in Excel with some formulas (see attachment) but am wondering if it is possible to do this in an Access Query (i.e. GroupBy the note number and have a calculated field combining the individual lines of the note, or something similar)?
In a query, I'm using concatenation to join multiple address fields (organisation, building number, street address, etc) into one field. I'm also inserting line breaks to create a formatted address and then creating a form on the query so the formatted address appears in a text box and can be copied and pasted straight into my label-writer software to print address labels.
This is the concatenation code I'm using:
Expr1: [Organisation] & Chr(13) & Chr(10) & [Building Number] & Chr(13) & Chr(10) etc.
However, one problem with which I am now presented is where a field is blank. E.g. if one of my contacts is retired, then they don't have an organisation listed in the "Organisation" field. This means that a blank line is left between their name and the rest of the address.
When running a mailmerge there is the option of skipping blank fields so that a blank line is not inserted in such cases.
How can I achieve this within the above code?
Gary
I have a query that is set up to join two name fields...for example:
[ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]
The issue is that this is perfect for names that are entered in the table like this:
Lstnam: ABC HOSP Fstnam: ITAL
They are entered like that for groups, but when individual doctors are entered, the data entry folks are entering them like this:
Lstnam: SMITH Fstnam: BOB Titlcd: MD
So when I join them the first displays correctly as ABC HOSPITAL, the second displays as SMITHBOBMD and I have to manually go in and add a comma.
Is there a way to do an if/then statement or something to tell the query to display results If the titlecd is NOT NULL, display as [ELIGVENDORS.FSTNAM] & [ELIGVENDORS.LSTNAM]&", "&[ELIGVENDORS.TITLCD], else display as [ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]
I'm sure there is, I am new to IF/THEN statments though and was hoping to get some help setting it up.
Thanks in advance and I hope my description is clear enough.
Han
Hello,
i am trying to join the results of a many-side of a query into one result field separated by commas...
Table: tblWorkOrderStoresFinal
Row 1: LogNumber: 1StoreNumber: 2
Row 2: LogNumber: 1StoreNumber: 3
Row 3: LogNumber: 1StoreNumber: 4
I would like to make a query that combines the StoreNumber into one field CombineStoreNumbers separated by commas as follows...
LogNumber: 1 CombineStoreNumbers: 2, 3, 4
I tried the following crosstab query, but it's not quite right.
TRANSFORM StoreNumber
SELECT LogNumber
FROM tblWorkOrderStoresFinal
WHERE (((LogNumber)=1))
GROUP BY LogNumber
PIVOT StoreNumber;
Any ideas? Hope this makes sense.
Thanks, Casey
There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.
Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)
I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.
So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.
Also, I have created the string, called "emailsList"
Code:
Private Sub Email_Button_Click()
'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home"));
Dim emailsList As String
DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True
End Sub
Do I need to do a loop that concatenates?
I have a query that I want to concatenate some of the results onto a single row, but I'm having trouble.I've followed Allen Browne's example, but when I run the query, I either get the #Type, #Name or a 3201 error (I believe, I can't recreate it now).
View 4 Replies View RelatedI have five reason fields in my table and I would like combine them into one field on my report, with line breaks between so each reason starts on it's on line. Also if there is a blank record, I would like the report not to display a blank line. I have found ways to do this online for 2 records but I need this for 5.
Also when I do manage to get the lines all to show in the report there are 3 paragraph returns between them - making the reason display of the report 15 lines tall, not just 5. The code I have so far, which displays each of the reasons but spaced as described is:
=[REASON1] & " " & [REASON2] & " " & [REASON3] & " " & [REASON4] & " " & [REASON5]
I am trying to concatenate 2 unique values which are parts of 2 identical rows(records) in order to have only 1 row(record) with concatenated field where the values are unique. For illustration here is what I need to achieve :
Initial table:
Fld1 Fld2 Fld3 Fld4
AAA XXX BBB CCC
AAA YYY BBB CCC
Final Table:
Fld1_____Fld2______Fld3__ Fld4
AAA____XXX,YYY___BBB___CCC
Hi
I did a search to no avail...
I am trying to concatenate Address fields into one text box. The Control Source pull-down menu gives me the Field Names listed as QueryName.ColumnName.
I know that if they listed the fields as just ColumnName, you'd simply go:
ColumnName & " " & ColumnName2...
But the way these columns are defined, I cant find the correct syntax.
Is there a way to do this without changing all my queries??
Thanks again! :)
Mike
I have three tables: Event related on to many with Procedure Procedure relates on a one to one basis with the description of the procedure in a table called ProcedureCodes.
I wish to have a query which outputs three columns:
Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.
I have tried the Allen Browne module [URL] .... This gives me:
Event - Procedure1, Procedure2, etc using the expresion:
Expr1: ConcatRelated("[Procedure Code]","[tbl-procedures]","[tbl-procedures].[Event number]=" & [Event no]) to concatenate the procedures.
But I am struggling to get the final column! I have tried the following:
Expr2: ConcatRelated("[short description]","[tbl-procedure code]","[tbl-procedure code].[ProcedureCode]=""" & [Procedure Code] & """" And "[tbl-procedures].[Event number]=" & [Event no])
But this does not return what I want.
I have a report that I'm trying to concatenate some "sum" and "count" data into one line. This is what I have
Code : "There is currently" & " " & =Sum([pounds]) & " " & "Pounds in" & " " & =count([quant]) & " " & "Containers"
but it gives me a syntax error when going from Design view to Report View.
I have 8 checkboxes. Each checkbox has several e-mail addresses as string. Therefore, each checkbox has a string variable declared. I was wondering what should I do when selecting multiple check boxes. This is my code:
Code:
If Me!chkAGDLLA = True Then
Forms!email.lstName = Null
Forms!email.chkComercial = False
Forms!email.chkOperacional = False
strAGDLLA = "email1, email2, email3, email4"
strMail = strAGDLLA & ", " & strISA & ", " & strMAYA & ", " & strSANGER & "," & strSANSE & ", " & strSede & ", " & strGC & ", " & strCSR
MsgBox strMail
End If
Problem is that if I only select chkAGDLLA, then strMail will be "email1, email2, email3, email4, , , , , , ,"
I do not want all those commas, but how to make this work. I was thinking maybe a SELECT CASE so that strMail will accumulate data based on what's checked, but then there will be a problem of there being no comma between cases.
I have a query that has multiple IDs and different information in numerous fields. For example:
ID Field1 Field2 Field3
1 x
1 m
1 b
I need to find a way to concatenate the data so that it shows the information like this:
ID Field1 Field2 Field3
1 x m b
I'm working in Access 2010.
Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.
I saw the following code on the forums...
Dim i As Integer, c As String
With Me.ListBox.Column(0)
For i = 0 To Me.ListBox.ListCount - 1
c = c & Me.ListBox.ItemData(i) & ", "
Next
End With
c = Left(c, Len(c) - 2)
This works great!! Now I am trying to concatenate the column 3 of the listbox.
Tried to use the code below (pointing to the second column) but it always returns the first column data.
'Dim i As Integer, d As String
With Me.ListBox.Column(2)
For i = 0 To Me.ListBox.ListCount - 1
d = d & Me.ListBox.ItemData(i) & ", "
Next
End With
d = Left(d, Len(d) - 2)
ItemData does not allow pointing to my 3 column of the listbox.
I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:
[Field1]&", "&[Field2]&", "&[Field3] and so on.
However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?
I know that it isnt advised to store the concatenated value but in this case, I have a memo field which is the description of a particular system component.
i have Unbound Combo Boxes for descriptive elements such as COLOUR, SIZE, CONNECTIVITY, MAKE.
ALL of these i would to store in a field in my table and called Description and separate them by colons or the actual words which describe the category.
Please Advise
Hey guys I'm developing a DB to input satisfaction surveys and compute satisfaction levels expressed as a percentage. I am setting up the DB with 1 record=1 satisfaction survey. The record would contain the fields:Survey Quarter and questions 1,2,3, etc. The responses to the questions are in pulldown menu form with Yes, No and N/A. I've already set Yes to be read as 1 and No as 0. Now I want to add across the row (Questions 1-11) so I can complete the math and get a percentage for each survey. So far I have had zero luck accomplishing this. Any suggestions?
View 4 Replies View Related