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 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.
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
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.
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»)
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.
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.
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?
View 3 Replies View RelatedI 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
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. :)
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
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 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 RelatedIn 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 am trying to calculate how many days are left till end of contract...
whats wrong with this formula
=DateDiff("d",[Completion_Date], Date())
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!
Hi,
In the query field i am putting: -
Consecutive Months: Query_Month_Consecutive([Report_2_group_data].[Availability], array([Monthly_availability_CT].[12],[Monthly_availability_CT].[11],[Monthly_availability_CT].[10],[Monthly_availability_CT].[9],[Monthly_availability_CT].[8],[Monthly_availability_CT].[7],[Monthly_availability_CT].[6],[Monthly_availability_CT].[5],[Monthly_availability_CT].[4],[Monthly_availability_CT].[3],[Monthly_availability_CT].[2],[Monthly_availability_CT].[1]))
Basicly the values of them fields are entered into the query, all fields exist and everything is ok.
Anyhow the function is not loaded, i have tested with a simple msgbox and it does not do it.
However when i simply do
Consecutive Months: Query_Month_Consecutive([Report_2_group_data].[Availability], Monthly_availability_CT].[12)
It works and the msg box appears?
I really need it to be an array for what im doing any help would be great
Thanks
k0r54
Would I use an update query or function to complete my task?
Task: Automatically update [Status] based on DateDiff calculation of [RequestDate] and [DueDate]
Issue: [Status] can be one of 6 values, the user can manually select Review or Completed.
[Status] values: Review; Completed; Due in 24; Due in 24-48; Beyond 48; Over Due
Query: Below is the query I just started to ignore the calculation if either Review or Completed exist.
Expr1: IIf([Status]="Review",[Status],IIf([Status]="Completed",[Status],DateDiff('d',[RequestDate],[DueDate])))If the value from above DateDiff equals to the below, I want to update the [Status] field to the [Status] value.
Due in 24 = (0-1)
Due in 24-48 = (2-3)
Beyond 48 = (greater than or equal to 4)
Over Due= (-1)
This is really weird:
I have one text field [Lot Number], and another calculated field Left$([Lot Number],3) As Grade in a query. I'm getting the following error:
Function is not available in expressions in query expression Left$([Lot Number],3)
This query was working fine up until a few days ago when all my users started having the same issue.
If I use the expression Len([Lot Number]) As Length as a field, I get the correct length of the field value for every record.
Any thoughts here? Anyone else encounter a similar problem?
I am trying to resolve an issue of being able to use a query from many different forms where the query is dependent on the date selected in the current open form. Right now it calls the function getDate() which works fine. Unfortunatly the function is called before the form is fully loaded and the control I want to pass has no value/doesn't exist yet and I get a
"Runtime error 13 Type-Mismatch"
Here is the function code:
Function getDate() As String
If fIsLoaded("F_SupplierData") Then
getDate = Form_F_SupplierData.txtDate
Else
getDate = "01/01/1901"
End If
End Function
Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function
Is there a way to detect the state of the text field itself (if it is open, closed, dirty)? Or if anyone has a better way to tie a query to multiple forms?
Hi,
I am trying to notify users to fill a specific field in if they forget it and can not work out which event to use.
Can you please help
Cheers
g
I have a questions database. When user is filling out the form, the following fields are required: Questions, Author, Type of Question and Answer selected. Answer is selected by clicking on the button next to the Answers. If these fields are not filled out, a user gets a prompt saying that so and so field is blank. If have a problem, it works for all required fields except for Answer. Below is my code. I have attached a screenshot with Author and Answers blank. I only get a warning about the Author and not the Answers.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
nullerr = 0
strnull = ""
If IsNull(Me.txtQuestion) Then
nullerr = 1
[code]..
I have a table with CODE number as text. (1234-11-3456). I also have this field in a subform. How do I get the field to start at left, and also make all CAPS. Also where do I do the input mask. In the Table or the Form where it is a subform?
View 4 Replies View RelatedI have several comboxes where I'm using date/time input masks. When I go to enter data in the field the cursor is situated at the right end of the box. I have to backspace to the beginning of the field to enter the data. How do I position the cursor to the beginning (left side) of the field?
View 3 Replies View Relatedhey,
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