How can I change a number column in a three digit percent one?Tried the following, however it just deletes the content of the column:
Code:
Public Sub TEST()
Dim a11 As Double
Dim year As Integer
[code]...
I have a datasheet with 7 columns. Two of the columns I'm working with are listed below.
File # Region #
2DE2-12345 2
3DE2-@@@ 3
In data sheet view I would like the Region # column to autofill based on the first digit of the file #. So, if file # entered = 3DE3-@@@@ then 3 would auto fill in Region Column. (data is entered in data sheet view)
I have used Left ([File #],1) to get value but cant make it auto populate the Region # column......
I have set a field in table to be a 'percent' However, try as I might am unable to type in 40 and get 40%
I seem to get either 4000% or 0. I have tried differing settings.
Has anybody else had this trouble and could offer some advice?
Thanks :confused:
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"
Hi all, I am happy with my fax database until my goverment decided to add another 6 infront of all the phone and fax number.
How can I change all the numbers together?
Example from : 1234567 to 61234567
Thanks.
hey guys i have the following line in my query for removing seconds from a time/date stamp and all works great except one thing if the time is 10:02 it is showing as 10:2 anyway of getting it showing correctly?
(Day([HTRXTBL].[HTRX_TIMESTAMP]) & "/" & Month([HTRXTBL].[HTRX_TIMESTAMP]) & "/" & Year([HTRXTBL].[HTRX_TIMESTAMP]) & " " & Hour([HTRXTBL].[HTRX_TIMESTAMP]) & ":" & Minute([HTRXTBL].[HTRX_TIMESTAMP]))))
I need to run a query that converts a 19 digit number stored in a Short Text field to a hex string
Example of the string is "3310854670615838865" the result should look like "2DF284C801000091"
I can't display a 17 digit number in my table without losing the last couple of digits to 'rounding'. I've tried 'doubling' the field size but to no avail....whatever I attempt loses the last couple of digits to a 'nice round figure'.....
The numbers had initially been imported as text...which is really what they ought to be as they're identification numbers, but I had some issues using the find 'duplicate values' query and conjectured that was because the ID numbers had been defined as text---->though I could be wrong.
The VAL function works (to convert the text to numbers) but again -- I lose the detail of the last couple of digits.
I've been beating my head against this wall the entire day and at the very least, would like to know if what I'm attempting is viable. I've stumbled through function queries (with some success) and react like a deer in the headlights when it comes to VBA....
Hi.
I have major report due within hours.
I need to add a field to existing table that will be a 9 digit number, non duoplicating or - to take an existing field of 9 digits and allow it's values to change to random or incremental, non-duplicate numbers of 9 digits. I already have an autonumber field I must keep.
Russ
i have a jobref (pk) as text at the moment because i tried it as Number data type and had a problem..a reference number is made and when the date loses focus the refno is displayed in its textboxthis makes that refnoPrivate Sub cbojobdate_LostFocus()Dim maxRef As Variant, maxID As IntegerDim codeDate As String, maxDate As String codeDate = Format(cbojobdate, "MMYY") maxRef = DMax("jobref", "job", "jobref like '" & codeDate & "*'") If (IsNull(maxRef)) Then 'test for new month maxID = 0 'reset id to 0 Else maxDate = Left(maxRef, 4) 'get date code maxID = CInt(Right(maxRef, 4)) 'convert to int End If Me.cbojobref = codeDate & Format(maxID + 1, "0000")End Sub8 digits.. so a job on september 6th 2006 will have a ref no of 09060001a job on february 1st 2006 will have ref no of 02060001when i send the jobref to another form using this..Private Sub cbojobfrom_AfterUpdate()If Me.cbojobfrom = "t1" ThenMe.cbojobfrom = "LHR - T1"End IfIf Me.cbojobfrom = "t2" ThenMe.cbojobfrom = "LHR - T2"End IfIf Me.cbojobfrom = "t3" ThenMe.cbojobfrom = "LHR - T3"End IfIf Me.cbojobfrom = "t4" ThenMe.cbojobfrom = "LHR - T4"End IfIf Me.cbojobfrom = "h" ThenMe.cbojobfrom = "LHR"End IfIf Me.cbojobfrom = "ga" ThenMe.cbojobfrom = "Gatwick Airport"End IfIf Me.cbojobfrom = "gn" ThenMe.cbojobfrom = "Gatwick North"End IfIf Me.cbojobfrom = "gs" ThenMe.cbojobfrom = "Gatwick South"End IfIf Me.cbojobfrom = "st" ThenMe.cbojobfrom = "Stansted"End IfIf Me.cbojobfrom = "lc" ThenMe.cbojobfrom = "London City Airport"End IfIf Me.cbojobfrom = "lu" ThenMe.cbojobfrom = "Luton Airport"End IfDoCmd.OpenForm "job_cash_inflight", , , "[jobref]='" & [jobref] & "'"End Suband the job_cash_inflight form loads it with this..Private Sub Form_Open(Cancel As Integer)Me.[jobref].DefaultValue = Forms!job_cash_single![jobref]End Subthe problem is when i choose february the first for example02060001.. when the reference number transfers to the incoming flight form.. it displays as 2060001can anyone see why because before it transfers the refno.. the original job record has already been saved.. maybe when the id is being created it is not the best way.. or it doesnt match the text datatype.. if that is the case what number datatype shall i use in each of the tables jobref appears in?
View 14 Replies View RelatedI have a table that has just Months in a column. As in January, February......
I want to convert these to Numbers as in 1-12 in VBA. I tried all the DatePart in every combination but can't seem to get the results.
I have a subform that displays doubles in a textbox as fixed with 4 decimal places as default. I have been trying to change the number of decimal places based on the value in a textbox on the main form. I have tried this:
Code:
Forms![Main Form]![Holder_subform].Form![Final PCT].[DecimalPlaces] = CByte(Forms![Main Form].Numdecs)
where [Final PCT] is the textbox on the subform containing the decimal number and Numdecs is the textbox on the main form containing the number of decimals I want to use. I want to implement this Form_Current sub.
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?
View 1 Replies View RelatedI have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name.
Example:
Starting file
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC
05512,3,2,4,2
ENDING table needed
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, NEWCOLUMNname
05512,3,2,4,2,2 ORIGIN2 ORIGIN4
Where the new column name contains the MIN number in the row and all of the associated duplicates column names.
I have a table with an auto number PK. This table will contain orders. I'd like to use the PK from this table as the Invoice number on the invoice. I'd like to have it start at a number other than "1" just because it looks better on an invoice. I don't know how to do this. I looked at the table design to see if there were options available to me there but couldn't find anything. Is it possible? (I do not know how to use code.)
View 7 Replies View RelatedI 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.
Can I edit a column nameto show a different name than in my table as a heading?
View 2 Replies View RelatedI'm using Access 2010. After creating a new database the standard screen appears that contains a grid to enter data and across the top of that grid is a row which is supposed to contain column titles. According to Microsoft Access all I have to do is double-click the column title so that I can set it to whatever text I want. the cursor remains in the next row assuming I want to enter some data. Why can't I change column titles from their default values?
View 3 Replies View Relatedi have a form that will have a column called [animalID] which is a 5 digit number. Only the first numer really matters. If the five digit code starts with a:
1,2,3,4 = cat.
5,6,7,8,9 = dog
example
animalID
29878 - cat
67424 - dog
47812 - cat
92435 - dog
I want my form to show "CAT" or "DOG" not 92435.
How do I do that? thanks.
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.
Hi :
I have a question,
Table1:
Company, Start Month, End Month, Fees, Calc
ABC_________Mar-05___Mar-05____7 ____1
ABC_________Mar-05___Apr-05____7_____2
ABC_________Apr-05___Apr-05____5_____4
I used crosstab to make this format
Company, Start Month, Fees, Mar-05 , Apr-05
ABC_______Mar-05 _____7_____1_______2
ABC_______Apr-05______5 ____________4
I want to change column position as following order:
Company, Start Month, Mar-05 , Apr-05, Fees
ABC_________Mar-05____1_______2_____7
ABC_________Apr-05____________4 _____5
I tried to use this code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("report_crosstab")
qd.fields("Fees").OrdinalPosition = 4
It doesn't work for crosstab query.
Can anybody tell me how to change the column position of the query?
Please let me know, thanks.
I have a Graph that uses a crosstab query to generate the data. Its only a simple query. One of the fields is project type, but in the query this changes to a number instead of text? I would like to have this as text but don't know how to go about it. I have searched forums and tried Allen Browne's suggestion on specifying column headings but no luck.
I have attached the initial query and the crosstab query in the screen shot as well as the graph. My aim is to get the project names in the legend, instead of 1, 2 as shown.
When I originally created my access database, I used a text field/column to connect 2 tables together. I would like to change this to reference the Primary Key/ID column instead of the text field.
Is there an easy way to go about doing this? My original thought was to create some vba code to replace the data in the column of the sub table with the autonumber in the primary table but I cannot figure out how to do it.
I'm making a db that will import a spreadsheet from Excel, then separate it into 3 different tables using queries, but then I need to put all the 3 different tables together one after the other. The problem is that the 3rd column on each table have different names on each table, so I can't append the tables together.
So, how can I use a Macro or something to change the column name on all 3 different tables so that they match and can all get appended together.