i want to make a string that contains all of the fields in a column.
I have a table called UserSelectedComponentT with a column called ComponentName.
I want a string that is essentially all the different component names seperated by a " + " .
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code:
Private Sub Command11_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
I want to convert a number column into a text column.
Number Column is called Customer.
Below I can't match to the other table containing the text column Customer No.
How can I convert columns in vba?
Code:
strSQL = "SELECT CALCULATED_Forecast_TNS.*, USEFUL.TRP_in_EUR " & _
" FROM USEFUL INNER JOIN CALCULATED_Forecast_TNS ON (CALCULATED_Forecast_TNS.[Reference No] = USEFUL.[Reference No]) AND (USEFUL.[Customer] = CALCULATED_Forecast_TNS.[Customer NO])"
Set qdf = CurrentDb.CreateQueryDef("REPLACABLES", strSQL)
DoCmd.RunSQL "UPDATE REPLACABLES SET CALCULATED_Forecast_TNS.TRP_in_EUR = USEFUL.TRP_in_EUR"
I'm currently working with a form, which is in datasheet view. I have many rows which are combo boxes (yes/no), and the name is rather long. So each line (each row) spreads on to 2-3 pages to the right.What I would like to do is make the namebar, on top of every column, a little bit higher, so the name would be split into two lines, or three. Allowing me to make the width allot smaller.Here is an example of my problem:http://213.213.137.96/~terminal/columns.jpgSo my question is, can I change the height of the column name? Or is there some trick I can use?regardsFrímann Kjerúlf
View 1 Replies View RelatedMS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
I'm new to VBA - Access but just dangerous enough to get in trouble. Is there a way to make a variable with the code below? I've been scouring the net all day and can't find answer. What I would like to do is make all the "cirsize1" be a string. Particularly the "Me.cirsize1.ForeColor". if I use the "cs" string it works fine with the Me.cirsize1 = 0 (ie "If cs = 0") This is just a snippet of the code.
Private Sub cirqty1_AfterUpdate()
Dim cs As String
cs = Me.cirsize1
If Me.cirqty1 > 0 Then 'if there is a quantity greater than 0 in circle qty and
If Me.cirsize1 = 0 Then 'if there is 0 in circle size
Me.cirsize1.ForeColor = vbRed 'then make the circle size 0 red
Me.txtCirIPM1 = 0
End If
End If
End Sub
This returns the correct value Debug.Print [myTable]![myField]
Why does this result in an error? Debug.Print Eval("[myTable]![myField]")
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.
I have a report that has only 2 columns (Routes and Carriers)
The report extends downward and takes up 3 pages.
I want all of the data on one page and when the data string extends to the bottom of the page, i want it to start over again at the top of the page (right where it left off at the bottom)
so i'd like it to look like the following all on one page
Route Carrier Route Carrier Route Carrier
AAA-1 Star AAA-7 Behnke AAB-4 Star
AAA-2 Titan AAA-8 LaidLaw AAC-1 Star
AAA-3 Star AAA-9 Star AAC-2 Titan
AAA-4 Universal AAB-1 Star
AAA-5 Star AAB-2 Star
AAA-6 Star AAB-3 Universal
I am migrating a database from Spreadsheets to Access 2010. Everything else is going well but I am stuck at one point. A table has Name, Gender, and Nationality fields. Now, I need a report that will have only Nationality and Gender fields. The nationalities will be in a list and another column should have total count for each nationality. Then, the Grand total should be print at the bottom of the report. Moreover, two other columns should have a count of each gender (male and female) against every nationality.I need Report which will have Four columns i.e. Nationalities, Total, Male, Female... The nationalities column will contain a list of nationalities that are there in the data table [field name: National].. The total column will count and show the sum of each nationality from the data table [same field: national]... The Male and Female columns will do the same i.e. count the occurrence and show the total for Male and Female from the data table.
View 6 Replies View RelatedI have 5 columns in ms access. They are as follows:
date_from, date_to, season_price, normal_price, actual_price.
However, I want to check if the sysdate is greater than date_from and less
than date_to then the value in the actual_price should be taken from the
season price for that record else the value should be taken from normal_price.
So, i would like to know how can do this? do i need to use visual basic?
I have no idea about visual basic. So, would like the complete way to do it.
Thanks
Hey Guys!
I have a problem concerning the ability to make a reference to another column in the same query.
I have two columns. Column 1 works perfectly, but in column 2 I want the outcome of column1 (coachingdato1) inserted instead of #17-07-2006#.
How is that done???
Thanks in advance!
//JR
Column1:
coachingdato1: (SELECT Max(coaching.dato) FROM coaching WHERE sælgerdata.sælgernr = coaching.sælgernr)
Column2:
coaching1: (SELECT coaching.[1] FROM coaching WHERE sælgerdata.sælgernr = coaching.sælgernr AND #17-07-2006# = coaching.dato)
I want to make a calculated column that adds 1 for every new record that isn't marked as being 'deleted' and will possibly subtract one if a record is marked as deleted. I'm not sure where to start other than calculated column.
View 5 Replies View RelatedI use a Make-Table Action query to import data from a linked table into my database. The linked table is on a network server that is automatically updated.
One column of the linked table is named "QTY/PARTIAL" and approximately 10% of the 500 records have a "P" after a number i.e. 1000 P. I would like to separate the number and the P into separate columns in the new table to faciliate being able to compute the total number "QTY".
Thanks,
Gunner...:confused:
I have a replica at work, 1 copy on the network, 1 local.. I need to add a column to the main table, getting "Operation is not supported for this type of object".... I have users that use the database but was given a change to the form/report which requires a column addition. What is the simplest way to get around this??? I'm trying not to get into major problems.
View 1 Replies View RelatedCan make a column show true or false in a table when two other columns in the table match each other? I think the statement would be like this:
If([Column1] = [Column2], True, False)
The only problem I am having is that I don't know if it can work in a table or does it just have to be in a query?
I have a table called Locations that lists Countries and Cities:
USA, Detroit
USA, Chicago
USA, New York
UK, London
UK, Liverpool
UK, Birmingham
France, Paris
France, Le Mans
I then have another table for inputing details on people I know. This table would have columns called Country and City. I've figured out how to make the Country column a "lookup column" so that I can only enter USA, UK or France. But how do I make the City column show only the appropriate list of cities relevant to the country that I've selected (e.g. Paris or Le Mans if France is the selected country).
DoCmd.RunSQL (" update tbl_userinformation SET [05-Henrichpiramid] = Yes where Username= Text146.value AND actualdate=Text148.value ;")
DoCmd.RunSQL (" update tbl_userinformation SET [combination] = [05-Henrichpiramid] where Username= Text146.value AND actualdate=Text148.value ;")
i want to update the column combination to its last value with concatanation to the value of current column.
I have a table that contains:
item | quantity
123 | 1
456 | 10
789 | 4
I need to get this data listed into a table that just has item and for it to look like this:
123
456
456
456
456
456
456
456
456
456
456
789
789
789
789
I understand and have accounted for the obvious primary key issue.how to get the data to display out like this.
How do I make a column invisible in my subform when the main form loads?
View 3 Replies View RelatedI've set the column widths properly on the subform, but the down arrow on the list box is still outside of the selection area. It may have something to do with lookups or concatenations, but I'm not finding that so in what I can think of to trace down.
I exaggerated the width of the list box to show where the down arrow actually shows itself.
See the form "frm_Class_Skills_Update" in the attached database. I need it to be visible when the list box is about 1.25" more narrow.
I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.
Here is my code.
trimmed_department = Trim(Replace(new_department, vbCrLf, ""))
Example value being "123 Point 5 Finance and Accounting"
I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.
I want to add a string as year to a date.
Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.
Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
I have set calendar control 12.0 up and everything works how I want it to (click date and peoples names in a table to the left to show scheduled meetings on that day). What I want is to add a string on top of the calendar. For example, this monday I would like for it to say "Memorial Day" on the physical calendar itself.
View 3 Replies View RelatedI would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...
View 4 Replies View RelatedI have made a system for managing service calls and fieldworks.Part of this is checking the boards we install/service are working correctly.When the engineer calls in, we check the board - enter serial number, via ODBC talks to main server DB and pulls back what is listed below, along with ID and date/time which is all displayed in a list box.
9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75
This works fine. In the string above are certain bits of information that need to be checked to ensure they are accurate and the board has been programmed correctly. What I want to do it, is when the user single clicks on the list box, it parses out the various sections of the above string and enters them into some text fields on the form. With some code these can then be checked to see if they are correct or not and alert the user if they are or not.
The checking part I can do, the part I am struggling with is parsing out the correct parts from the string.The parts will be the same parts required each time, and the string parts wont vary, just not sure how to go about it.