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"
how I can convert the first column to show the number? I used the expression below, but it does not convert anything with a letter or a 0
Expr1: Val([SPPC TEST]![JULY_ACCRUAL_ADJ_X])
I have an expression that converts a string to positive/negative number. It seems to be working fine, however, it doesn't convert "00000000001}" to -10. It shows as 10.
This is the formula that I am using in my query:
PJUNAccrual: IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",-1,1)*(Val([PJ_ACCRUAL_ADJ_X])/IIf(IsNumeric([PJ_ACCRUAL_ADJ_X]),100,10)+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "A" And "I",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-64)/100,0))+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-73)/100,0)))*100
I have a field called PropertyID which stores a unique 13 digit number, the 10th digit is always a 3. I want to remove the 3 to leave a 12 digit number.
I've tried running an Update query using Replace([PropertyID],"3","",10,1) but no joy, what am I doing wrong?
I have a field on my Report called Revenue. It is used in a calculation and there is a calculated field called Profit. Revenue-Cost=Profit. That all works.
The only thing that is wrong is that the Revenue field displays its numbers like this:
0.7
12.43
4.3
0
etc.
i.e. not formatted for currency. But I don't get the currency option under Format. It is based on a Query and this Query is based on a Linked Table.
Any suggestions on how to get it formatted as currency while still having the calculated field working?
Hi!
I have two tables Atable and BTable and both have a field called AcctNo. In Atable, it is type of Number and in BTable, it is type of Text. When I do a join with these two tables
select * from Atable, BTable where Atable.AcctNo=Btable.AcctNo
I get error saying "type mismatch".
My question here is: How can I convert a type from number to string or string to number in my sql statement?
Thanks,
Aijun.
I have a form that takes input to the first, indexed, field from a barcode reader (and keyboard at a pinch). The data coming in has always been numeric so I have several checks in place to ensure it is numberic (field specification) and also that it lays in a predefined range. All works well! I've now been asked to enable this to work with a single letter prefix e.g. T1234567.
As there are many forms which use this same field I'd like to keep it numeric and as the prefix won't change I can add it later using a query. Is there a way to programatically strip the first letter from the string and use CLng() to convert to numeric using the on entry Event Procedure? All my other validation is here but I can't see a way to do this. Failing that, can I use an unbound text box to take the entry and set the index field?
I have been trying to convert string into double number format. I am running a SQL query in VBA that returns a double number format; however my understanding with SQL queries in VBA is that they return string only. The results are showing up perfectly fine when I run the query in the query editor; however when I try using the returned value in further calculations in VBA I keep getting a "Type Mismatch" error.
View 7 Replies View RelatedIn my query I want to extract the last 10 characters of a string in a column which represent a date in the format DD.MM.YY and then convert these to a real date format to be available for further processing.
My query looks like this:
SELECT Angebotskopf.[Laufende Nummer], Angebotskopf.Angebotsnummer, Angebotskopf.Angebotsdatum, Angebotskopf.Anfragedatum, Angebotskopf.Kunde, Angebotskopf.Ansprechpartner, Angebotskopf.Telefonnummer, Angebotskopf.Faxnummer, Angebotskopf.Projekt, Angebotskopf.Preis, CONVERT(varchar(10), RIGHT(Angebotskopf.Projekt, 8),104) AS TestAngebot
FROM Angebotskopf;
But Access gives an error message "unknown function 'CONVERT'"
The "RIGHT" functions works but the resulting column is not being recognized as a date, it is a only a string and therefore useless for processing of any date related calculation.
I am importing a table from a Clarion TPS database which stores several types of data in a "IDVAL" field. The field next to it, "LBLNUM" defines what the data type is. This creates a problem, as IDVAL is a text field, which means all data stored in it (no matter what the type) is then stored as text. This includes date fields. Since they are stored as text, they end up as 5 digit serial dates, similar to how excel handles dates (with each day after 1/1/1900 being +1).
So I have a union query which derives all the values stored in IDVAL for each unique record. My query which creates my export combines this data with several calculated fields to create my final export.
Everything looks great on this final export except for my date fields, which are all 5 digit numbers (like 77945).
I have been searching for a date function that will format this back to a standard date, however I have yet to find anything that works. Is there no way to format a 5 digit serial date back to a standard date within access by using a function? Every answer I find says to redefine the table to a date field, however I don't have this option since the field in question stores a lot more than just dates.
I am in the process of building a new database in SQL to replace my MS Access database. However, I will continue to use the Access forms, queries, and reports. The new tables will house much of the same data. In multiple tables I have hyperlinks that were created and added in the original Access tables. To import these hyperlinks into the new SQL tables I have converted them to 'Long Text' before exporting, thus changing them into strings.
For example:
Hyperlink - Email - Add Additional Mailbox to Outlook (2010) has been changed to:
Email - Add Additional Mailbox to Outlook (2010)#ServernameServerfolderDocumentationRea dy to GoOutlook TemplatesEmail - Add Additional Mailbox to Outlook (2010).oft#
The obvious issue that I am running into is that after the SQL database table has been linked to the Access database it still displays the entire string when I open the table. The form has a textbox and search button that is used as a search function. This runs a query that returns all "search results" for the desired information. Is there a way that the query can convert the string back into a hyperlink so that the query displays just Email - Add Additional Mailbox to Outlook (2010) as a hyperlink and not the entire string?
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 need to convert a number string to a date For example, 0820 i would need to convert it to august 2020 ( the date will always be in this century)
View 3 Replies View RelatedTrying to convert a date in my query to a number. What function will convert 6/17/2015 to 42172? Tried datevalue dateserial
View 3 Replies View RelatedIs it possible to easily convert 02/02/2015 15:30:00 to 201502021530 in a query?
View 4 Replies View RelatedI build a database to manage some events. To simplify things, let's assume an event takes place on 3 days, from 01-05-2013 to 03-05-2013.
I create a table named EVENTS.
Among other stuff, I create the following fields:
EVENT_first_date
EVENT_last_date
Then I create a query which returns the number of days the event will take place on.
EVENT_total_days: [EVENT_last_date]-[EVENT_first_date]
I use an append query to insert this new record into the table EVENTS_MAIN.
I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.
Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.
Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?
I have a query which runs fine, however one of the outputs is a calculated field and i use the Format(someValue,"Fixed") method of specifying the format for the % Change .Here is the query
Code:
SELECT tblTempBearsWeek3.SymbolCode AS [Short Ticker], tblTempBullsWeek3.SymbolCode AS [Long Ticker], tblTempBearsWeek3.[Week 3 Date], tblTempBearsWeek3.Short, tblTempBullsWeek3.Long, tblTempBearsWeek3.[Short Week 3 Close], tblTempBullsWeek3.[Long Week 3 Close],
[code]...
The problem is that the % Change calculated field for some reason comes out as a String. i know this because when I try and sort on that column, it sorts it as if it is text and not a number value i.e. it doesn't put it in Ascending order.
Is there a way to convert a phone number in text format into a number and remove any dashs or parenthesis. What function can I use ?
old format (951) 244-3011
new format 9512443011
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 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 RelatedHow 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......
The dates in my table are strings that appear as 12305 (1/23/05) and so forth. So some are 5 and some are 6 digits depending on the 1 or 2 digit month. I want to convert them in my query to a date field. I think I have to use the DateSerial and extract the parts of the date, but that leading 0 that is not there is throwing me off. Any easy solutions?
View 2 Replies View Related