I have table where First Name, Last Name and Initial are all in one field. What is the syntax to remove initial? Been playing with Right and Len with no success, I'm sure this has been done before.
View RepliesI'm having difficulty with the syntax in this query to remove duplicate data for the field "StocktransID".
Code:
SELECT DISTINCT tblStockTrans.StockTransID, tblItem.Brand, tblItem.Category, tblItem.SubCategory,
tblItem.Model, tblItem.Description, IIf(TransTypeID=3,Quantity*-1,Quantity) AS Qty,
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType,
tblItem.Origin, tblOption.ParentID
[code]...
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
How do i trim a name in a string.
I have 2 rows with firstname in one and the second has surname. if i wanted to view the results to have A Einstein rather than Albert Einstein, how would this be done?
thanks.
Hi!
I think this is propably an easy thing to do, but nevertheless I have not been able to make it work in MS Access...
(Let's say) I have a table with information about people. Sometimes I need to get a subgroup of the table_people, like a group with a certain age, or who live in a certain area, or who like certain things. And sometimes a need to work with the whole table.
To be able to do this basic filtering I have a form with check boxes, like "check this if you want to filter by a persons hobby" and appropriate fields where to choose a hobby from a list. My problem is: how do I add this "condition" to my query? I've tried eg. using "Like" as a filtering tool and IIF() to add the condition, but this doesn't work (in the criteria row):
Like (IIF( Forms![basic]![Filter_by_hobby] , "[Forms]![basic]![Hobby_from_list]", "*"))
So the idea is that if the box is checked, the condition is true and the first text is chosen, and if no filtering by hobby is required, all people are chosen regardless of their hobbies (Like "*")
How to fix this - is there a syntax error or a profound mistake in my thinking - or how to do the same more elegantly?
Thanks in advance,
garcanrya
I have a query written in Visual Basic as follows
UA1 = Forms(ParName).Form.NOM
UA2 = Forms(ParName).Form.PRENOM
UA3 = Forms(ParName).Form.CARTE
Forms(ForName).Recordset.FindFirst "[NOM] = '" & UA1 & "'" & " And " & _
"[PRENOM] = '" & UA2 & "'" & " And " & _
"[CARTE] = '" & UAE & "'"
This query works and I can spot the record based on 3 fields and display it. Now I have discovered that we have a person who has an apostrophe in his name like O'Brian. The above code gives a syntax error in this case.
I have a combo box on a form where I allow the user to pick an office. The primary key is the bound column which I then want to use in a stored query to limit the query results to the specific office selected. I can easily do this with the docmd.runSQL in the module, but I'd like to use a stored query instead. My exact problem is I can't get the syntax correct in the Where clause in the query design. I use the build feature and get the following: [Forms]![frmReports]![cboOffice] which doesn't work. If I manually put the key value in the Where clause, it works fine. tell me the correct syntax for the Where clause.
View 4 Replies View RelatedTrying to parse first two sections of a five section string. Now getting a SYNTAX COMMA ERROR. Th Left((([ArborID],InStr([ArborID],"-")+1,(InStr(InStr ([Arbor ID],"-")+1,[ArborID],"-")-InStr([ArborID],"-"))-1)),10)
View 14 Replies View RelatedCode:
Dim AppendUsers As String
AppendUsers = "INSERT INTO tblPermissions ( empid )" & _
"SELECT tblEmployee.empid" & _
"FROM tblEmployee LEFT JOIN tblPermissions ON tblEmployee.[empid] = tblPermissions.[empID]" & _
"WHERE (((tblPermissions.empID) Is Null) AND ((tblEmployee.active)=Yes) AND ((tblEmployee.inputname)=Yes));"
If MsgBox("This will search for new users with valid logins and add to the permissions table, do you wish to continue?", vbYesNo, "Append Users") = vbYes Then
DoCmd.RunSQL (AppendUsers)
why the first code is working and after adding a few lines it gives me a syntax error. It highlights the AS ETI. I usually work on the query grid, but I read somewhere there is a limit to the length of expression. So I ended up editing the expression in SQL view, but somehow doesn't work.Working before editing:
Code:
SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked, IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))) AS ETI, DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks, Sum([Salaries YTD].Gross) AS SumOfGross1
[code]...
I have 2 tables called MakeTable1 and DBO_TBL_Activity
Im trying to update MakeTable1 with the values from TBL_Activity when both activity.StartDate and maketable1.Dates match but also acticity.IDStaff and Maketable1.ID Match
Below is the SQL i have so far
Code:
UPDATE [MakeTable1].[Detailsa] SET [dbo_tbl_activity].[details]
WHERE [MakeTable1.Dates)=[dbo_tbl_activity].[StartDate] AND [MakeTable1].[id]=[dbo_tbl_activity].[idstaff]);
The error is :syntax error in update statement
I can't seem to figure out the proper syntax for the FindFirst method. I am using several variations of this effort:
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset)
rst.FindFirst "rst!ID = frmInvoice!txtID"
I get an error message that says that Access doesn't recognize rst.ID as a valid field. But, it most certainly is. I tried substituting tblInvoice but got the same error.
why this query comes back as invalid syntax?I am attempting to find all records older than two quarters, Month of Contact actually displays the date as ##/##/####
View 4 Replies View RelatedCorrect syntax/expression for log of a number. The number i am getting in Access is twice as big as the correct number i get in excell
View 3 Replies View RelatedHow to DELETE rows in one table that have a field value that matches a field value from another table? Tried these but sytax errors:
DELETE * FROM tmpBankDebitsMinusJE
WHERE tmpBankDebitsMinusJE.ConcatenateBankDebits IN
(SELECT tmpJournalEntryChangeOrders.Concatenate);
DELETE FROM tmpBankDebitsMinusJE
WHERE ConcatenateBankDebits IN
(SELECT Concatenate IN tmpJournalEntryChangeOrders);
I'm trying to replace a null value with $0.00 for the second field in a query. My first try at the SELECT stmt did not contain any solution for a NULL value. The result was that it skipped the record. I need it to show 0.00 because the field is used in another calculated field.
My SQL:
SELECT tblRecovery.CustID, IF((Sum([tblRecovery.RecAmt]) IS NULL, 0.00, (Sum([tblRecovery.RecAmt]))) AS SumOfRecAmt
FROM tblRecovery
GROUP BY tblRecovery.CustID;
This returns : Syntax error (missing operator) in query expression 'IF(( etc.
After clicking "OK", access highlights AS in the statement. I'm not sure how to deal with the NULL value or fix the error?
I am creating a database to analyze prices. I have 2 tables. One table has a bunch of dates, product names and the prices. There are many of the same dates and many of the same products. I.E. a product could get priced one day and than have a different price the next day.The other table has a bunch of information and dates and product names also. The goal is to add a week to the dates in this table and use that date and product name to look up the corresponding price for that date and product and add it to the record.
I am trying to use Dlookup but evey record is then filled with the same price. I am using this Expr1: DLookUp("Price","Qry_Historic_Price","[Date]=#[End Period]# And primary_id ='[primary_id']")...When I run the query I get a message that says "Syntax error in query expression" than a message box that pops and says "Unknown" with only "ok" to click.
i have just taken over
I have just taken over some database which have a load of tables and queries that I suspect are no longer requirer. Is there anyway to see when a table or query was last USED in a database??
Any help is appreciated
explain me the Query Expression to remove the first 7 characters in a column, to rum a Query.Eg - I have entry like - 4/8/11-Pipe...I need to return the result like "Pipe".How to build the query.
View 5 Replies View RelatedI 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?
How to create an expression that removes the ".tif" extension of a file path.The data looks like this in it raw form:
J:201303080056273_ELECTRICALRETAILER_00100562 73_ELECTRICALRETAILER_001_3.tif
In my query grid in the filed row I have the following:
ImageRef: Mid([strImagePath],111,38)
which produces the following:
00056273_ELECTRICALRETAILER_001_3.tif
00056273_ELECTRICALRETAILER_001_31.tif
and so on...
What I need to do is remove the ".tif" part of the data. because of the way the path is output with regards to the tif image number, I'm having difficulty in targeting only that data that comes before the ".tif" extension.Is there a method I can use that will remove the right 4 characters and in conjunction with my expression above produce the following:
00056273_ELECTRICALRETAILER_001_3
00056273_ELECTRICALRETAILER_001_31
I've managed to work out how to get the data without the file extension ".tif" with the following expression:
ImageRef1: Left([strImagePath],Len([strImagePath])-4)
which gives me the following:
domgennt.dggroup.comglobalResourceApplication sUNIeFlowIMAGES201303080056273_ELECTRICALRET AILER_0010056273_ELECTRICALRETAILER_001_3
and
domgennt.dggroup.comglobalResourceApplication sUNIeFlowIMAGES201303080056273_ELECTRICALRET AILER_0010056273_ELECTRICALRETAILER_001_31
How can I combine the following expression with the above expression to get what I need:
Combine - Mid([strImagePath],111,38) with Left([strImagePath],Len([strImagePath])-4)
to get this result:
00056273_ELECTRICALRETAILER_001_3
00056273_ELECTRICALRETAILER_001_31
I have a field called "SC" with a value 13-251. I need to remove the "-" ending up with 13251. I tried using, as a starting point, Mid(String, Start, Length) with no success. I found this in another topic, and thought this might be close to what I was looking for. I sure did not know how to use it. When I selected the run command I got the following response:
Query must have at least one destination.
I tried a few different things like adding an extra field and different pointers but I doubt if I'm even standing in the batters box.
I have a weight field that I've been populating with the weight and the unit of measure. I want to seperate those into 2 different fields. I've created a new field called UnitofMeasure and now I need to go back and remove all the instances of 'oz' and 'lbs' from the first field.
Is this possible with SQL?
hi, im a bit stuck. i am creating a mail merge and i want access to retrieve only the first character of the forename to input into the mail merge.
e.g. Dear Mr. R Price.
I want the 'R' to be retrieved by access from the full forename, so 'R' would be retrieved from the name Robert in the forename field.
Thx
I run duplicate check on a form to make sure the file name doesn't match one that's previously been entered. Currently I have:
If Me.NewRecord Then
If Not IsNull(DLookup("File", "tblFileProcessing", "[File]=""" _
& Me![File] & """" & " and [FileClientID] = " & Me![FileClientID])) Then
DoCmd.OpenForm "frmDuplicateFiles", acNormal, , , , acDialog
End If
End If
However we've recently changed the way we work and have now added a unique ID number prefixed to the file name: 567_File_Name_1 How do I run the DLook up but parse off the digits prior to the first underscore and only check on the remainder of the file name?
I have a form set up so that a supplier code combo box is populated with a simple SQL statement to get values from a lookup table. I have a second combo box that is populated when the supplier code is selected by the user. It works well. For example, if the user selects supplier Dog then the current record value of the Product field is displayed and the combo box shows all Dog products. So that's great.
My problem arises when the form loads. I can't convince Access to fill the Product combo box with the current products. For example, if the first record has Cat as the supplier, I want it to display the Product value for the first record and to show all the Cat products in the drop down. It isn't doing it on load or on record navigation. It does work if the user reselects the supplier.
I have tried various events.
Right now I have the following code in the AfterUpdate event of cboSupplier:
qry = "SELECT SupplierCodeProductCodeEquityTypeQuery.Product.Nam e, SupplierCodeProductCodeEquityTypeQuery.ProductCode ,
SupplierCodeProductCodeEquityTypeQuery.SupplierCod e, "
qry = qry & "SupplierCodeProductCodeEquityTypeQuery.Supplier.N ame, SupplierCodeProductCodeEquityTypeQuery.ProductId FROM SupplierCodeProductCodeEquityTypeQuery "
qry = qry & "WHERE SupplierCodeProductCodeEquityTypeQuery.Supplier.Na me = '" & cboSupplier.SelText & "';"
Me.cboProduct3.RowSource = qry