Left Function

May 2, 2005

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.

Any thoughts would be great. Thanks.

View Replies


ADVERTISEMENT

Using Left Function

Jul 20, 2005

I have a column in access containing codes in format K2316 and wish to remove Prefix (K) from this code.

How can i do this as a calculated field?

thanks

View 14 Replies View Related

Left Function

May 17, 2006

Hello,

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,

Thanks,

J.

View 4 Replies View Related

Need Some Help With A Left Function

Sep 13, 2007

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

Mail Zipcode «Expr» Left («stringexpr», «n»)

View 3 Replies View Related

Probelm With Left Function

Mar 11, 2006

Hi there,

I have a query with Expr3: Left([POK],2) as one of my fields.

Whenever I try to run the query it pops out a message window saying

Syntax error (comma) in query expression.

Please tell me what I am doing wrong.

Thanks in advance.

View 2 Replies View Related

Left Function In IIF Statement

Feb 4, 2008

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.

View 2 Replies View Related

Update With Left Function

Jan 9, 2012

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?

View 3 Replies View Related

'Left' Function No Longer Working??

Jan 8, 2008

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.

Any ideas?

Thanks

View 5 Replies View Related

Possible To Use Function: Left(Field,4) In A Query?

Aug 19, 2006

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 ;)

View 5 Replies View Related

Left Function To Extract First Four Characters

Nov 27, 2006

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.

Please help if possible. :)

View 11 Replies View Related

Queries :: Left Function With 2 Static Characters

Jul 23, 2015

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?

View 6 Replies View Related

Between Function - Show All Records If Left Blank

May 10, 2013

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?

View 4 Replies View Related

Queries :: Left Join On A Query After ConcatRelated Function

Nov 2, 2014

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.

View 1 Replies View Related

Queries :: Left Function - NOT Ignore Leading Zeros

Aug 3, 2015

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?

View 3 Replies View Related

DateDiff Function - Calculate How Many Days Left Till End Of Contract

May 18, 2013

I am trying to calculate how many days are left till end of contract...

whats wrong with this formula

=DateDiff("d",[Completion_Date], Date())

View 14 Replies View Related

Using The Left Function Inside A "NOT LIKE" Comparison

Jun 29, 2006

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!

View 6 Replies View Related

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

So basically I need making a function that will count the number of records from another table/query based on a field from the current query.

View 2 Replies View Related

Forms :: SUM Function Produces Error From Calculated Function

Jan 30, 2014

I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/

The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.

Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |

So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total

=SUM([Qty Unit] * [Unit Price])

All fine and well..... However, the additional functionality kicks in.

Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.

Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.

Code:

Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "

[code]....

its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.

=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error

View 2 Replies View Related

Take Left Of Another Column..

Mar 25, 2008

Not sure if this is possible? I have a column called Issues and another column I want to call ShortIssues and I basically want ShortIssues to be Left(Issues,50) so that its just 50 characters long of column Issues.

any ideas.

Thanks,

View 11 Replies View Related

Right Or Left In Query?

Jun 15, 2005

hey,

Is it posible to somehow intigrate this code or some other code that would do the same in to query?

code:
str1 = Right(Me.Text1, InStr(Me.Text1, "Ex") + 2)

I have a table1 with filed "EXZ" and that field contain some data like "7.5/400/Eex de IICT4" now i would like to run an update query on that table and update field "EXZ" from "7.5/400/Ex de IICT4" to "Eex de IICT4". The last part of the data is not allways the same but it does allways start with "Ex"! So is that even posible to do with query or do i have tu use a macro or what?

THX

View 3 Replies View Related

Left Join

Nov 21, 2005

Hi
I am trying to use left join on the following tables.
Employees
----------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME

and

Weekly_Timesheets
-------------------
EMPLOYEE_ID
WEEK_NO
YEAR_NO
TOTAL_HOURS_WORKED

All I want is to pull all the employees with their corresponding total worked hours. There are cases when some employees forget to fill their time sheets in a week and might not have entries in weekly_timesheets table. Since I want all the employees irrespective of whether they have a record in weekly_timesheets or not I am using left join.

The left join I am using is as follows:

SELECT EMPLOYEES.EMPLOYEE_ID,
WEEKLY_TIMESHEETS.TOTAL_HOURS_WORKED
FROM EMPLOYEES
LEFT JOIN WEEKLY_TIMESHEETS
ON WEEKLY_TIMESHEETS.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID
AND WEEKLY_TIMESHEETS.WEEK_NO = 46;


The moment I am trying to execute this statement Access is crashing. I couldn't figure out what am I doing wrong.

Kind Regards
Bhanu

View 6 Replies View Related

Left$ Query

Dec 8, 2005

Left$([Entry timestamp],14)

I want to execute the above sql query but the result is not what i'm expecting.
the field entry that i wan to extract the leftmost 14 characters is
2.005113E+19
instead of just having plain numbers like 2005113009010532000000.....

I think it's becoz of the format that's why Left function is not working as expected. Can somebody help me out regarding this? Thanks very much

Regards
Dominic

View 8 Replies View Related

Left Join?

Jun 22, 2006

Hi Guys

I'm kind of a newbie to Access but am learning :D
I've found myself stuck with a join statement

I have 2 tables: tblFleet & tblService

I need to find the most recent service date from tblService for each entry under tblFleet....

This is my code thus far, but it shows all the records for both tables where the fleet_Num field is equal....

SELECT tblFleet.Fleet_Num, tblFleet.In_Yard, tblService.SrvDate
FROM tblFleet LEFT JOIN tblService ON tblFleet.Fleet_Num = tblService.Fleet_Num
ORDER BY tblService.SrvDate DESC;

View 2 Replies View Related

Left Join

May 8, 2007

Simple exlusion query:

SELECT tblEmployees.EmployeeID, tblEmployees.LicenceNo, tblTimeSheet.ShiftDate, tblEmployees.FirstName, tblEmployees.LastName
FROM tblEmployees LEFT JOIN tblTimeSheet ON tblEmployees.EmployeeID = tblTimeSheet.EmployeeID
WHERE (((tblTimeSheet.EmployeeID) Is Null));

errrr nope it doesnt work.. and that is direct from the wizard...

I have a work around but it is inelegant not to mention not workable..

what i need to do is filter on a shift date as i need to see what employees are not rostered on particular dates..

what i have had to do is make a query selecting base data from timesheet and filter the date at that point... make another query to do the left join.. again not elegant... to make a report i would somehow have to programmatically adjust the filter on the date.. too hard basket and will be too slow when i have a copious amount of data in 12 months... anyone know what the hell is wrong?

View 5 Replies View Related

Left Join

Nov 14, 2007

Hello

I have a loan db that I want to write a query to reflect all active loans with or without a current month's payments. In other words, I want it to select all active loans even if there was no activety during the month. I have 1 query that pulls out all active loans; qryActiveLoans. I then create a left join (2) with that query Grouping on the Loan# and summing the Principal and Interest fields where the payment Date is between #10/01/07# and #10/31/07#. I need to group on loan the number because some loans have more than one transaction in the month.

The problem is that the query does not select the loans that didn't have any payments during the month. I thought that a left join (2) will pull out ALL the records from the qryActiveLoans query. I've tried adding the word ALL after the SELECT statement but that does not work. I've also tried various forms of nz without success. This is probably a common problem but I could not find it in my searches of past postings.

Any help would be appreciated.

View 4 Replies View Related

Almost There, One Form Left!

Sep 10, 2005

I've created a db for my mortage company and I have everything complete except returning customers. I want the customers to have the same customerID but different TransacionID. Sounds simple to me but im having problems. It could be a table issue but im hoping its just a form thing.

I have tables: Customers-CustomerID(autonumber),CustFirstName,CustLastName, PhoneNumber,EmailAddress,OriginatorID.

Then I have Table: Detail- CustomerId(lookup from customer table),TransactionId(autonumber),SSN,Address,City, State,Zip,Spoucename,CreditScore,

Then I have Table: Loan- TransactionId(lookup from Detail),LoanType,LoanAmount,StartDate,EndDate, LoanLender


I have a form for new customers which is the customer table. Then once the customer has his credit checked we enter the customer details with a form. Then when they want to move forword with a loan there is a form that pulls the TransactionID from the Detail Table.

I cant figure a way to make a form that will create a new transactionID associated to the custs CustomerID without overwritting the older record.

What do I need to do?

Thanks for any help.

Scott

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved