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 Replies


ADVERTISEMENT

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 Three Characters From String Using Query

Sep 8, 2005

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.

View 1 Replies View Related

Left Join Query - Help Needed

Nov 7, 2005

I am trying to build a query that shows me the activity of my customers. I have a database that amongst other things holds two tables which i am trying to query.

tbl 1 - Customers
tbl 2 - Enquiries

What i eventually want to do is create a report that shows which customers have made enquiries and which ones haven't on a weekly and monthly basis.

the fields brought out of tbl1 are just the companies name, and sales person responsible for the account. Now to get all the customers to show i have learnt that you can do a left join on the query which will show all the records in the left table (tbl1) which is great and i can then limit the customer accounts that show by adding query criteria to the sales rep field from tbl1. The problem that i am having is when i try to limit the time period i.e weekly, monthly. when i enter criteria in the EnquiryDate field from tbl2 the query stops showing all the customers and only shows those that match the date criteria. Can anyone tell me how to prevent this from happening.

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

Problems With Left Join Query

Apr 17, 2008

I need bailed out again. I'm not getting the desired results with the following query:

http://img.photobucket.com/albums/v155/randman1/query.jpg


There are a total of 11 records in the tblSystemAlarms table. The desired result is a return of all 11 records for a particular ReportID, even if there is not a record in tblSystemAlarmStatus. The actual result is a return of only the number of records that contain a matching ReportID. How do I include the other records?

View 5 Replies View Related

Extract Data To Left Of Dash In Query

Jun 20, 2005

I need to break up part of the value in one of my fields. I can do it in Excel by identifying the position of the dash with eg. =FIND("-",D13) And then using it in a LEFT function. Or using the text to Columns wizard.

How can I do it in a query, there is no find function. If there is an equivelant what is it?

Sample data:

22DF-RED
33AF-0
44TG-Blue


I need only the data to the left of the Dash

Thanks

Ziggy

View 2 Replies View Related

Unable To Return Query If A Field Is Left Blank

Dec 27, 2007

Hi all,

I am having a problem getting my query to work properly. I have read through this query section but it just got me more confused. I know some have used IIF function but it didn't work for me.

here's my code:

SELECT tblEmployee.UserName, tblODF.ODFNumber, tblQueue.Queue, tblStatus.Status, tblODF.ODFScanDate
FROM tblStatus INNER JOIN (tblQueue INNER JOIN (tblEmployee INNER JOIN tblODF ON tblEmployee.EmployeeID = tblODF.EmployeeID) ON tblQueue.QueueID = tblODF.QueueID) ON tblStatus.StatusID = tblODF.StatusID
ORDER BY tblEmployee.UserName, tblStatus.Status, tblODF.ODFScanDate;


I want everything to show even if one of the fields is blank.

Thank you

View 14 Replies View Related

Queries :: Left Joint Query Type Mismatch?

Aug 27, 2014

Working with two tables - tbl_A_OrdData & tbl_B_ShipData tbl_A have all the orders placed, but tbl_B only have the orders that got shipped. I'm trying to create a query that will show me all orders that got placed for a particular criteria and if the order find a match in tbl_B to give me the ship date too. A left joint query get me the data I need. But my dates are in YYYYMMDD number format so in the query I am putting the DateValue function. And that error out as "data type mismatch" because it cannot find the shipdate for certain records. I tried this but didnt work. ShipDt: IIf (IsNull ([PSHPDT]), "", DateValue(Mid([PSHPDT],5,2) & "/" & Right([PSHPDT],2) & "/" & Left([PSHPDT],4)))

View 2 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 :: Nested Query - Left Outer Join Not Returning All Records

Oct 1, 2014

I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..

Code:

Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity
from(
SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items]
)a
left outer JOIN [Open Items] AS [Open Items_1] ON
(a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where ([Open Items_1].MonthEndDate=#8/31/2014#)

View 1 Replies View Related

Queries :: Left Join Query To Show Records That Fulfill Two Criteria

Mar 25, 2014

I have two queries. The unique key in both queries is GUID for katalogposition.

One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.

The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.

The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.

1. No match in second query "yq_NonTermination"
2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".

I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.

How can I do that? Is there another way to do it? Please see attachment.

View 3 Replies View Related

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

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

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

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

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

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

Left Pad With A Zero Digit

Oct 6, 2004

Hi, when I type 0600 in a text box, it dispays it as 600. How do I make it so it displays the number that I actually type in, with a zero as the first digit?
Please help. Thank you.

View 6 Replies View Related

Left Join

Oct 25, 2004

Hi,

I am trying to do a left join in ASP but getting error message saying "Syntax error in From clause"
The query runs fine if I have a record for a country in all the tables. But I have certain countries that do not have a
matching record in currency table. For those countries I want the information from the other tables. Following is the
query:

SQL = "SELECT Country.Country_Name, Country.Continent," & _
"Country.GNI_per_capita, Inc_Cons_Distribution.Top_10_percent," & _
"Inc_Cons_Distribution.Gini_Index, Population.Total_Pop, Population.Urban," & _
"Population.Rural, Population.Under_14, Population.Below_Poverty," & _
"Currency.Curr_Name, Currency.Exchange_Rate FROM (([Country] LEFT JOIN Population ON " & _
"Country.Country_Name=Population.Country_Name) LEFT JOIN Inc_Cons_Distribution ON " & _
"Country.Country_Name=Inc_Cons_Distribution.Country _Name) LEFT JOIN Currency ON " & _
"Country.Curr_ID=Currency.Curr_ID where country_ID in(" & countryList & ")"

Thanks in advance.

-GreetInfo

View 1 Replies View Related

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 4 Replies View Related

Application Being Left Open

May 19, 2005

I have a shared 2000 db and for the last few days someone has been leaving it open. In '97 you could open the .ldb file and see who it was, is there a way to find out in 2000?

Also, I have seen something on remotely removing users who leave the application open but can't find it now. Does anyone know how this is done? The data must be refeshed daily and hasn't been for the last four days due to this lock.

View 2 Replies View Related

Left And Right Arrow Keys

Apr 13, 2007

I would like to have a event for the left and right arrow keys, does anyone know how to do this?

View 4 Replies View Related







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