I have to query a record with 2 of the same static characters. "-".I can get the left function with the first " but I can't get the rest up to the second "-"
Example: B-4352B-PXP02W01-10
TagNo: Left([EventInstanceID],InStr([EventInstanceID],"-")) Brings me the first char plus "-" The next section is needed also but the remainder is not.
Needed: B-4352B
Is there a Mid Function that I could add to go to the second chaacter instead of stopping at the first one?
I have seen variations of this question and have tried to modify for my purpose, but have been unsuccessful. I need to extract the first four digits of the Social Security Number. We no longer view the whole number, but a variation of the first four of social and first four of first name. I have tried many variations of the Left Function to extract the data from the field:
Expr1: SSN(Left([SSN],4)) Expr1: Left([SSN],4)
Etc. I get various errors, mostly involving the comma. I am at a loss since I usually search for the code and find that it works. In this case, it doesn't work. I am also trying to extract the first four of the first name and will later combine the two although I broke it down since I couldn't get the basic "left" function to work.
I managed to produce a working Query with AllenBrowne's ConcatRelated function.What i'm unable to do is a new query with left join on the result of the query using the ConcatRelated function.I get error 3075: syntax error (missing operator) in query expression (free translation - MS ACCESS not English version)
If I do the normal join then it works fine, but I only get rows for which there are related concatenated values. But I do also need those rows where there are no concatenated values' fields.
In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have
caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year) officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)
This was working fantastic until I had to enter cases from 2009, ex: 0911000587
I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).
Is there anything I can do to force it to NOT ignore the leading zero?
I have the following string: NE1220904 I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!
In MySQL I can use the LEFT(3, field_name) command, but I essentially want access to pull all records from a table where the the first three digits of a number field match those pre-selected by a form drop down.
This database is in Access 97. Is there a way to compare the first x number of digits using SQL only, or do I need to run each line through code first, and then check it (I really don't want to do that)?
example numbe rin field:
123456789
SQL checks to see if 123 matches value selected on a form.
I have a problem whereby I want to import an Excel file into my Access db but certain characters like >< it doesn't accept. what other options do I have?
I'm having to recode some old MS Access DBs so they will run in the following environments:
Office 2000 on WinXP Office 2003 on WinXP Office 2010 on WinXP Office 2000 on Win7 Office 2003 on Win7 Office 2010 on Win7
When I wrote my code for Office 2000 on WinXP things were simple because directory paths were the same across all computers and I could hard code pathing when using a shell command to launch other files.
My new approach is to make a function call to the Windows registry to determine the default executable and path for opening a file based upon its extension (see apicFindExecutable in basWindows API module).
I'm able to use code to create a shell call and debug print it to the immediate window. If I put my cursor in the immediate window at the end of the shell call and hit [enter] the external file will open as desired. If I try to open the external file directly through code, I get a file not found error.
To recreate the error take the following steps:
(1) browse to files that are accessible from your computer (2) click the PREPARE DATA AND OPEN MAIL MERGE DOCUMENTS command button
Shell function call is made by the fnOpenFile function located in the basOpenFile module. There has to be a trick here that I'm missing.
I have three machines. They all have random variations, but a machine could also have a static deviation. If the last 4 values are above 0, the deviation is considered static and should be corrected. In this example 111 has a static deviation
Nowadays the operators look at the graphs and when they observe the static deviation they will adjust it.
Question is: Is it possible to use a query to extract 111? In that case I can give operator a warning in stead of him looking at the graph.
I have used the left function to provide me with the left x characters of a field, which works fine.
Description: Left([c$products],[diff])
However, I want to query this field to only give me certain descrpitions, but it will not work. Can anyone let me know if this is possible, or if I'm totally out of luck,
I have an imported table with zipcodes, some are 5 digit some are 9. When i go to build my query i dont get an accurate number, i tried a left function to get the 1st 5 digits but keep getting a invalid syntax. I am in the query builder. anyone help me with syntax, heres what i currently have
Hi all - I have a database that has only 1 field of patient names (e.g., "Johnson,Peter S"). I also have a form that allows clinicians to lookup their patients and I want them to be able to type in the last name of their patient and get the info they need. Anyone know how to pull just the part of the name BEFORE the comma (i.e., the last name only)? The Left function won't do it because the last names are different lengths.
Also, is there a way to have the form pull all the names after each letter they enter? So when the user typed "J" it would pull Johnson, Jones, Jackowa, etc, but when they typed the "o" it would decrease the list to only Johnson and Jones.
Hi, hope someone can help me. I want to run a query which acts on data in a table which has been imported from excel, where it was somewhat mangled. Basically, the field shows telephone numbers, but the leading zeros on the phone numbers have mostly been discarded. I want to replace the zeros if they're missing. (Here in the UK phone numbers always start with zero). What I'm trying to use is: IIf(Left[fieldname],1)="0",[fieldname], "0" &[fieldname])) But I get errors, I think maybe because of the first comma(?). Can anyone tell me how to achieve this? Any suggestions appreciated.
I have a column with a date in it, and the left four characters are the year. I want to add another column that is just the year. I've tried doing an update query and updating the year column to left(date,4) but it doesn't work. How should I go about updating my year column?
I developed a database in 2005 and used the 'Left' function in many of the queries to obtain the the correct text for output to reports. The database worked extremely well for a year and a half.
I have not accessed it for the past year. Recently I opened the database for use with a new project and I keep receiving the error message:
! Undefined function 'Left' in expression.
I haven't changed any of the expressions and do not understand why this funtion no longer works.
Hi!In my query i'd like to format a selected field.At the moment i write it like this:MyOwnFieldName:[Table].Field I want to achieve:MyOwnFieldName: Left([Table].Field, 4) & "-" & Right([Table].Field, 4).Is this possible?Thanks for helpful feedback ;)
I am creating a query which uses 2 unbound text boxes to populate a Between function for 2 date fields. If I fill in the dates, it returns the corresponding data. If I leave them blank, however, it returns no records. Is there an easy way to tweak the query to return all records if left blank?
Hi, I'm trying to compare 2 name fields to see if the last name of one field is found in the last name of the other field. In table A, the name is stored as LastName, FirstName. I've used the Left and InStr function to extract the last name successfully. In Table S the name is stored as separate fields, Lastname and Firstname.
In a query I am trying to determine if the last name from table A appears in the last name field in table S
(s.lname Not Like '*Left([a.name],InStr([a.name],",")-1)*')
the problem I think I'm encountering is that I want access to see this statement as:
(s.lname NOT LIKE '*JOHNSON*') instead of searching for the Left function string
is there any way to put the value of the function in there without actually searching the function literally?
I tried [ ] and that didn't work.. any ideas? thanks!
I currently have two tables each containing information that I need to complete a query, however the results of suming this information is incorrect.
Below is the structure of the table, one contains more fileds than the other however the joins are on the Date and the Name fields. I have added a few dummy results to show the format
Table 1 "Date" "Name" "Times Logged In" 10/01/2013 Chris 1 11/01/2013 James 3
Table 2 "Date" "Region" "Manager Name" "Name" "Times Logged Out" 10/01/2013 Scotland Cindy Chris 1 11/01/2013 Wales Robert James 2
Query Fields
"Data" "Region" "Name" "SUM Times Logged In" "SUM Times Logged Out"
I can see what the issue is, if someone hasnt logged out there would be no entry in the table so the result query would show "Times Logged In" as X and a blank value at "Times Logged Out." However if there is a value at "Times Logged Out", "Times Logged In" is displayed again and causes the SUM calculations to be incorrect.
how do I overcome this issue though? I can do two spereate queries on the tables to produce results for Times Logged In and Times Logged Out but cant seem to create one query to display both these results in one table.
What I want is IF the date in Table 2 = Date in Table 1 AND IF Name in Table 2 = Name in Table 1 THEN take the value from Logged In and place that in a column and the value from Logged Out and place that in a column.
I'm having problems doing a left join with 3 fields. I know I don't need the 3 fields for this example to align the data but, I deal with much more than this and need this functionality.
The query object I'm looking into is named 'left join' and the sql is as follows:
SELECT Act_Base.CustID, Act_Base.Chipset_ID, Act_Base.Product, Act_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE FROM Act_Base LEFT JOIN SE_Base ON (Act_Base.Product = SE_Base.Product) AND (Act_Base.Qtr = SE_Base.Qtr) AND (Act_Base.CustID = SE_Base.CustID);
I'm expecting to see 150 units in the SE.Qty field.
how to find the first space on the left of a text string, for example:I have this string : FUJIFILM XM-1 16-50mm BLACK and I want to find the space between FJUJIFILE and XM and then get all the text from XM onwards.I have this expression that gets me so far but then I'm stuck:
I need to do a report that takes data from many different tables (like 10).
To do this I have a big query with many LEFT JOIN to link all the tables together. I have made this query last year and it worked well and efficiently until now.
But I have to add something there, and the problem comes with it :
One of the tables is a currency exchange rate table, with the exchange rate of USD to 50 different other currencies and for each day since Jan 1st 2011.
But in this table there are some dates when a currency does not have an exchange rate (example : it was a holiday in that country, so there is no official exchange rate with this currency on that day).
However, I need to have rates even if they are not present in the table.
I need to get the nearest rate, for example if the rate of EUR on 31st December is not available, I can take the one of the 30th, or 29th or January 1st...
Ideally I would like to do the following :
Add a new LEFT JOIN to my already big query that would look like :
LEFT JOIN CurrencyExchangeRate ON ABS(RateDate - Instrument.Date) < 3
But to take only the closest rate.
I tried with a subquery :
LEFT JOIN (SELECT TOP 1 * FROM CurrencyExchangeRate WHERE ForeignCurrency = Instrument.Currency1 AND ABS(RateDate - Instrument.Date) < 3) AS MRate ON MRate.ForeignCurrency = Instrument.Currency1)
But the subquery does not accept Instrument.Currency1 because it is not part of the table CurrencyExchangeRate
I have a solution that should work, it is to search for this rate in a subquery in the select clause instead of doing a left join. But the tables are quite big (hundreds of thousands of entries) and my work machine is not really 'fast' so it would take hours and hours (when I tried I stopped after 3 hours).
I know there is the solution to make a macro to "fill" the missing rates with the rate of the previous day... But I would prefer not to go to this extent.
I have a manual date format that looks something like:
02/16/2015 09:06:15 AM PST
I would like to truncate the text so that just the date is showing. For all that I have found, I can remove a string from the beginning? any tips on removing a string from the end?
