I have a calculated field which consists of a few other fields concatenated, i.e. [field1] = [field2] & [field3] & [field4].
I need field 1 to be unique when its concatenated, but fields2-4 don't need to be unique. Is there a way to do this?
I have downloaded a contact template from the internet ready to go. There are few things I need to get rid off and make changes to.
One of the most improtant thing is making one of the field unique. For some reason, i can get to the design view. I open navigation panel and right click on the table, there is no such thing call design view on the menu.
I have a Make Table query that includes a new field concatenated from a numeric field and a memo field, with some assorted text elements -
Issue Description:"("&[number]&") "&[Description]
When I run the Make Table, the new field "Issue Description" is created as a text field, NOT as a memo field.
Is there any way to insure that this field is created as a memo field.
Thanks.
Susan
I am using the following to add a column to an existing table. How do I make this column indexed with no duplicates?
db.Execute "ALTER TABLE [BrandTBL] ADD COLUMN UPCGroupName TEXT;"
Table1 contain Two fields (3Months) and (6Months)
Table2 contain Two fields (3Months) and (6Months)
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
My form has a button that has a calculated field on it (a dcount(...)). When the button is clicked, a couple of conditions are checked and if all is ok, it runs the vba code. If the tests fail, it returns to the form without running the code. All works correctly, except that the dcount number has disappeared. I finally figured out that it is now hidden behind the button. How do I bring it forward again programmatically?
View 11 Replies View RelatedIs it possible to make a calculated field in a table not allow duplicates?
There is obviously no option to select No Duplicates in the field settings?
I have imported a lot of data from excel into a table. There is in one of the columns some multi concatenate info I want to separate in it's own individual row.I am not sure if there is a way to do this in a query or if there was an MsAccess2007 example somewhere. I looked on the internet but couldn't find anything.
the columns are like this now
123456 7RFE01, 7RFE03, 7MFE06, 7MFH04
237890 8RFE80, 8MFE03, 9GFE06, 5QFH06, 3QFY06
I want the columns to be like this in a query or new table.
123456 7RFE01
123456 7RFE03
123456 7MFE06
123456 7MFH04
237890 8RFE80
237890 8MFE03
237890 9GFE06
237890 5QFH06
237890 3QFY06
Hello, Bit of a newby to all this. Am trying to use a text box as a calculted field. I am doing so using an sql statment in control source The sql stat is built and tested using build query and then copied and pasted from sql view)
I keep getting #name? as an error.
I have managed using dlookup of the same saved sql to return the required value (which works) but find it to be a bit slow and clunky so wanted to give this a go instead. If somebody can have a look at the query below I would much apreciate.
=(SELECT Sum(QryInout!CostExVATGBP) AS TTlCostExVATGBP
FROM QryInout &_
WHERE (((QryInout.StockImportNoJoinToAll)=[Forms]![FrmSPurch]![ImportNoNew])) &_
GROUP BY QryInout.ProdType &_
HAVING (((QryInout.ProdType)="Freight"));)
Note that I have tried with and without the &_ and also with or with out the brackets..
Thanks
I have 4 texts fields [DEVICE INSTALL] , [INSTRUMENT STAND], [BRACKETS RACEWAY SUPPORTS], [TUBING CAPILLARY] all can be either a percentage from 1-100 or "N/A" except [DEVICE INSTALL].I would like to calculate the [percentage complete]
my calculation in the field is as follows:
([DEVICE INSTALL]+
IIf([INSTRUMENT STAND]="N/A",100,[INSTRUMENT STAND])+
IIf([TUBING CAPILLARY]="N/A",100,[TUBING CAPILLARY])+
IIf([BRACKETS RACEWAY SUPPORTS]="N/A",100,[BRACKETS RACEWAY SUPPORTS]))
/4
this seems to work to a degree. but for some reason when the [INSTRUMENT STAND] value is not "N/A" the percentage calculated is a wacky value much higher than 100. how could I possibly change my division #?
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies View RelatedI'm creating a report to check for over- or under-stocked items. The report is working fine, gets all the records etc. except that it thinks that the OnHand field from my inventory query is text or something, at any rate not a number. I have successfully set the format of the field in the query to General Number, but that doesn't seem to have worked. Here's the SQL for the report:
Code:
SELECT DISTINCT Signs.SignCode, Signs.SignDescr, Size2.XYdim, qryOnHand.OnHand, Bins.Rack, Bins.Level, Bins.BinNum
FROM (Size2 INNER JOIN (Signs INNER JOIN (Items INNER JOIN qryOnHand ON Items.[ItemsID] = qryOnHand.[ItemsID]) ON Signs.[SignID] = Items.[SignID]) ON Size2.[SizeID] = Items.[SizeID]) INNER JOIN (Bins INNER JOIN InventoryDetail ON Bins.[BinID] = InventoryDetail.[BinID]) ON Items.[ItemsID] = InventoryDetail.[ItemsID]
WHERE (((qryOnHand.OnHand)<=[Check for signs with fewer than:]));
I would like to append a text item to each value in a calculated field. consider the field name is "Division" and I want to append the word "Division" to the values put out by the field "Division"
My attempt was: Division & " " & "Division". This produced an error in the report.
I have 2 fields in a query which I want to write to a table. Eg ProductName (field length 10) and Product Code (field length 5) the fields need to be filled out so the length makes the maximum text
For example product name 'test' length is 4 i have to add 0 to make the length to 10 so the field will be 000000test same thing applies to Product code. I have 700 records like this, is there a way in VBA where it adds the extra text to make it the maximum length.
I have a field in a table that is to be concatenated from two other fields, PolPrefix and PolNum. On a form I got it to show the full field 'PolicyNumber' by making the control source =[PolPrefix] & [PolNum].
But this doesn't update the field PolicyNumber in the respective table, and only shows it on the form. How do I get a concatenated field defined by the user into a table so that I can call that field other places in the database?
I am attempting to create a printed label with a concatenated field. I want the field to abbreviate several shapes and colors but i will just start with the shape.
=IIf([Shape]="round","RD","TH")
This is what my basic statement says. I am attempting to nest other options inside of this.
=IIf([Shape]="round","RD",IIF([Shape]="oval", "OV", IIF([Shape]="triangle", "TR", "TH")))
Is this the correct syntax? I can not seem to get it to work.
I can add to my make table query UDate: Date() that will add a populated field to the output table that has today's date in it.
I can also add something similar like this for example V_Num: [V_Number] and when ran, will prompt with a dialogue box to add some data, it will then create the table with that new field name and populated with the text from the dialogue box.
But how can I automate this to skip the dialogue box and just add it to the expression?
I am trying to sort with Docmd.Runcommand acCmdSortAscending
When it tries to sort a concatenated field i am getting the runtime 2046 Error
Code:
Private Sub cmbSort_AfterUpdate()
Select Case cmbSort
Case 1
Me.CompanyName.SetFocus
DoCmd.RunCommand acCmdSortAscending
Case 2
Me.DateCreated.SetFocus
DoCmd.RunCommand acCmdSortAscending
Case 3
End Select
I am using MS Access 2007 and have a report which display the list of values (say 10 fields) from the basic SELECT * FROM table query.
I need to dynamically set the BOLD based on the particular value for the fifth field which was assigned from Query. if it doesn't match it should display normal.
E.g
If Field_5 = "Agent" then
Field_5.FontBold = True
Else
Field_5.FontBold = False
End if
I tried the aforementioned code and it dont work. ALso i tried achieve it using below listed options.
Me.Field_5.FontBold = True
Field_5.Properties("fontweight") = 700
But nothing worked.
I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". For example, if costumer "Jack" has ordered as the following:
Code:
ID costumer product quantity unitprice orderdate
12 jack A 4 10 25/11/2014
13 jack B 5 5 25/11/2014
14 jack C 1 4 25/11/2014
15 jack F 3 2 25/11/2014
16 Jack G 4 8 25/11/2014
17 Jack H 1 7 25/11/2014
Then I make a report based on the above table and issue that as the invoice to the costumer, who is jack here.
How can I make a unique invoice number, which automatically increases sequentially for each invoice? Do I need to add a new column to the table? I also need to be able to look for a specific invoice based on the number, or date.
I am trying to run a make table query that involves using a linked table in Ms from a SharePoint list. However, a few columns I wish to include in my Make table query are calculated columns from the SharePoint list.
The make table query will not run saying "Calculated columns are not allowed in SELECT INTO statements".
Can any think of a solution or a potential work around to this?
I need to create a table with these columns in it.
I have a text box and currently this is my control source
="Testing " & [test]/3
test = 1000 so my text box reports:
Testing 333.333333333333
Is there a way to make it into a form like $333.33...Also is there a way to make [test]/3 come out in a money text form? like "Three hundred thirty three dollars and thirty three cents.
Just when i thought the db was finished i decided to let the boss check it.
What we have.
An Employee Database that has employee qualifications that each have a specific run out date. I fill in the database with all correct information the bos checks the db and wants to be able to do the following:
Open up forms as usual cycle through each employee (114) form and be able to write a specific message regarding that employee on that employees form.
Simple i said so i did the following:
On the form i have placed a text box.
THE FLAW
He writes something in the box and it appears on all the forms. WHY?
Not an Insult just my favorite saying!
Keep It Simple Stupid KISS
Is there a way to make just part of the text in a text box bold, or to use different font sizes in the same text box?
Something like this:
---------------------------------
Heading in bold: description in regular (not bold)
(a smaller height line used as a line space)
Another "normal" line
a larger height line
a normal line....all in one text box!
---------------------------------
One thing I'd like to be able to do is to specify the line height of a blank line in a text box. I'm using carriage returns created with: Chr(13) & Chr(10). I could see defining the font size of a hidden character, but I'd need to know how to assign a font size to a piece of the text in a font box.
is it possible with transform, only uppercase text contained in a report textbox, to bold? How? Through VBA?....
View 1 Replies View RelatedMy query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused: