Query with InStr Function.

i have a table with first name and Middle name in same field(length of names is variable). like

Simon K.
Ak D.
Paul LM.
Dixxions DYJ.

etc. i want to split them into two columns, so after space it should split the first name and middle name separately. like

FirstName........ MidName
Derake ........
Simon ........ K.
AK ........ D.
Paul ........ LM.
Dixxions ........ DYJ

for this purpose i have write a querry, but shows the result like that,

Fore Name .......FirstName........ MidName
Derrak .......#Error ....... #Error
Akal .......#Error ....... #Error
Simon K. .......Simon ....... K.
Ak D. .......AK ....... D.
Paul LM. .......Paul ....... LM.
Dixxions DYJ. .......Dixxions ....... DYJ.

The Query is like this.

	SELECT Samp.Forename, Left([Samp.Forename],InStr(1,[Samp.Forename], ' ') -1) AS Fname,  mid(Samp.Forename,instr(Samp.Forename," ") )  AS MidName
FROM Samp;

Any one know how to Replace the #Error signs to the Name(e.g First name & MidName)


Post your answer or comment

comments powered by Disqus
Hi, I'm trying to make a query with the Count function. Here is my code:

	SELECT tblFunction.JobID, tblContractorFunction.FunctionID, Count([TrackingNumber]) AS Expr1
FROM tblProductionInput INNER JOIN (tblJob INNER JOIN (tblFunction INNER JOIN (tblContractorFunction INNER JOIN
tblProductionInputDetail ON tblContractorFunction.ContractorFunctionID = tblProductionInputDetail.ContractorFunctionID) ON
tblFunction.FunctionID = tblContractorFunction.FunctionID) ON tblJob.JobID = tblFunction.JobID) ON
tblProductionInput.ProductionID = tblProductionInputDetail.ProductionID;

My probles is that I keep getting the following error:
You tried to execute a query that does not include the specified expression 'JobID' as part of an aggregate function.

Here is my data structure:



I have the following value in 1 field: Latoria V
This value clearly has a space. When I use the Instr function to return the position of the space, it returns 0 and it returns 0 for all of the records. The field has a first name, space, then middle initial. Why is this? This is th function I have:


That returns 0. What am I doing wrong?

I am trying to use MS Query to import queries from an Access database. All of the queries import fine except:
-One query uses 2 functions in a module to capture the beginning and ending dates in a form. The query works in Access, but when I attempt to import using MS Query, I get the error "Undefined function 'f_Beg_Date'(which is the name of my first function) in expression"
-Also, I have a query which uses 4 different subqueries to perform aggregates (Select Count(My_Field) WHERE blah, blah....) and when using MS Query to get it into Excel, I get the "Expected Parameter" error. Note that this isn't a parameter query.

Are either of these errors avoidable based on my needs or is MS Query just not robust enough and I need to start delving into Automation?

I am having a bit of a problem with my update query. i have a field that shows a forename. i am importing data from an excel file. The forename populates with forename and middle names and they are seperated by spaces as opposed to commas. I have used the following InStr function in my update query however it works fine when the records forename field has a middle name but it deletes all data in the records forename field if it contains only one name which many do. How can I adjust the function to ignore those records that do not hold more than one name in the forename field. As you can imagine some forename and middle name combinations hold many names.
For instance the filad may have Ivor as a name which I would want to keep But if the Field showed Ivor Bigun then Bigun needs deleting.

Left([Forename],InStr([Forename]," "))


I'm working on a project where users are interacting with an access database through a collection of userforms in Excel.

I've got an issue with a query that I try to run from a sub routine in excel. The query works fine when i runnit i access but when i try to execute it through a database object in excel(through vba) i receive a runtime error. The error states that there is an undefined function in the expression.
The aim of this query is to append all customers along with a datevalue (selected from a userform) to a table (provided that the record doesn't exist already). The table receiving the records holds customer, date and price.

i've encountered this issue or similar issues several times and would like to find a better solution to it. I feel all my ideas are a bit retarded and imagine that someone here has a way more elegant solution.

The ideas that i have thought of this far is
1) Create a temp table, delete old values, load new values.
Then the date values are available to use in a table and i have no trouble to create the append query
2) Create a VBA procedure where i iterate through the records valid for the date and compare with all customers and then append the ones thar are missing.
3) Create a macro which runs the SQL expression and then create an access application object and run the macro (using DoCmd.RunMacro) through excel (however this would require that all the users has a license for ms access)

Btw i also feel my SQL expression is a bit retarded. First of i tried setting using only a string in the second join criteria but that didn't work. However I did manage to get to it work by concatenate the field with a string and erase the field value.

	INSERT INTO MontlyResultValues ( CustomerName, ValidForMonth ) 

SELECT CustomerToSAPID.CustumerName, #9/1/2010# AS DateForInput 

FROM CustomerToSAPID LEFT JOIN MontlyResultValues ON
= MontlyResultValues.CustomerName 
 Cstr(Replace(CustomerToSAPID.CustumerName,CustomerToSAPID.CustumerName, '') & '2010-09-01')  
= Cstr(format(MontlyResultValues.ValidForMonth,'YYYY-MM-DD')) 

WHERE (((MontlyResultValues.CustomerName) Is Null))

the vb code where i receive the runtime error

	Private Sub ExampleSub()

Dim strSqlCommandText As String
Dim pubDatabaseForPfMng As Database
Set pubDatabaseForPfMng = OpenDatabase("C:Documents and Settings" & Get_User_Name & "DesktopExample.mdb", False, False, "MS
Access;PWD=" & strAccDbPassW & "")

strSqlCommandText = "INSERT INTO MontlyResultValues ( CustomerName, ValidForMonth ) SELECT CustomerToSAPID.CustumerName,
#9/1/2010# AS DateForInput FROM CustomerToSAPID LEFT JOIN MontlyResultValues ON CustomerToSAPID.CustumerName =
MontlyResultValues.CustomerName And Cstr(Replace(CustomerToSAPID.CustumerName,CustomerToSAPID.CustumerName, '') &
'2010-09-01')  = Cstr(format(MontlyResultValues.ValidForMonth,'YYYY-MM-DD')) WHERE (((MontlyResultValues.CustomerName) Is
pubDatabaseForPfMng.Execute strSqlCommandText, dbFailOnError

end sub

I have a function that takes 4 inputs and creates a record in a table (for accounting). I wanted to process a batch of records at once, based on the results in a query. So I made the query to give me the records I wanted to make accounting entries. I then made a second query that has a field with the function in it, that feeds the inputs off the first query. Everything seems to be normal,the query returns 5 records, the function runs 5 times and creates the records. Problem is, it creates SIX records. The first one is duplicated for some reason. There are 5 results from the query, but 6 records created. Here is the code of the function:

	Public Function AddRecord(tableopen As String, IDNum As Long, amnt As Currency, entdate As Date, descrip As String)

Dim dbnm As Database
Dim rstAccount As Recordset

Set dbnm = OpenDatabase("returnedchecks.mdb")
Set rstAccount = dbnm.OpenRecordset(tableopen)

    With rstAccount
        !IDNo = IDNum
        !entrydate = entdate
        !amount = amnt
        !description = descrip
    End With

AddRecord = "Yes"

End Function

Here's the SQL of my first query (creates the initial recordset):
SELECT tblChecks.*, tblChecks.IDNo AS IDNo2
FROM tblChecks
WHERE (((tblChecks.SentToDMV)=0) AND ((tblChecks.TagStatus)="2"));

SQL for second query (runs the function)

SELECT AddRecord("tblARDMV",qryDMV2!IDNo2,tblchecks!check amounttag,Date(),"Stage 2: etc") AS Expr1, qryDMV2.IDNo, tblChecks.CheckAmountTag
FROM tblChecks INNER JOIN qryDMV2 ON tblChecks.IDNo = qryDMV2.IDNo;

What is going on here? Why is it creating an extra record in 'tblARDMV'?

Thanks a lot!

Hey guys, can anybody help
here is what I have:




JobTask as you can see the relationship between Job and Task is M-M

PositionTask as you can see the relationship between Position and Task is M-M

ActivityTask as you can see the relationship between Activity and Task is M-M


TaskCompetency as you can see the relationship between Task and Competency is M-M


I have a query like this:

The user will input the JobId, PositionId, ActivityId.
I want to return all the TaskName ,CompetencyId, CompetencyName
Where the following:
The highest priority within the same keyword
Basically group by keyword then pick the highest priority I have done this:

SELECT Max(Competency.Priority), Competency. Keyword
FROM Competency INNER JOIN ((ActivityTask RIGHT JOIN (positionTask RIGHT JOIN (JobTask RIGHT JOIN task ON JobTask.TaskID = task.TaskID) ON positionTask.TaskID = task.TaskID) ON ActivityTask.TaskID = task.TaskID) INNER JOIN TaskCompetency ON task.TaskID = TaskCompetency.TaskID) ON Competency.CompetencyID = TaskCompetency.CompetencyID
WHERE (((JobTask.JobID)=[job])) OR (((positionTask.PositionID)=[position])) OR (((ActivityTask.GroupID)=[Activity]))
GROUP BY Competency.CompetencyKeyword;

It works fine, it returns the all the keyword, and its highest priority.
But I can’t return the Competency.CompetencyId, Competency. CompetencyName with the aggregate function Max

Given a simple table with historical info about Hotelroom occupancies such as name of the guest, date in, type of room, number of nights, ...
I want to make a query resulting in 1 output record that shows the highest number of nights someone has ever stayed in the hotel, as well as the corresponding detail information: who was this customer and which type of room was he in?
Using the 'group by' and 'max' function it's easy to solve the first question, but I am not able to show the corresponding detailed information in that record (person's name, type of room).
I've done some experiments with a nested query in the 'expression' field, also trying a selfjoin but always getting errors. I'm confident there must be a solution? Can anyone help me out? Thank you!

I am trying to make a query using the DateDiff function. I want to determine if the date of last customer contact is less than 365, between 366 and 730 or greater than 730, thus making three categories. My objective is to get the last contact date from a related table to get one of three variables, A, B or C which would represent whether the sales contact is prime, secondary or old.
I can calculate the number of days using DateDiff, by first using Max(tblSalesContactsFinal.DateofContact) AS LastDate to get me the last contact date. Then I used DateCount: DateDiff("d",[LastDate],Date()) to get the number of days. But when I use Iff(DateDiff("d",[LastDate],Date())730, "C", "B"))
I get the error Undefined Iff In expression. I tried it with only one variable, that is Iff (Value)

I've attached a sample database to attempt to explain better what I'm trying to do.
I'm trying to find the right combination of using the Mid and Instr function to extract each
series of numbers between dashes. With the sample query I am able to extact the first and seconds
series of numbers between dashes, but I can not get to go any farther than the second position.

I think for the third and beyond series it needs to be somthing like.........
Select numbers, numbers, Mid([numbers], Instr([numbers], Instr([numbers], "-"), "-"))
From Table1;
But I'm getting an "#error" on this query.

Hopefully this helps me better explain what I'm trying to do.

Thanks in advance.


I have a query with 5 fields. One field is called ICID. There may be 100,000 records and I want a query that will display the TOP 25 ICID's that show up the most. I'm trying to write the query but I'm doing something wrong and can't get it to work. Here is my query without the Count function:


How do I add the count function to get this to work. I've tried

ORDER BY Count(CELL109.Number_of_Events) DESC;

This does not work. I've looked through the archives and I see method I'm supposed to use but I still can't get it to work. I hope someone can help me here. Thanks.

Let's see if I can explain this situation effectively...

I've been handed a database that is set up to track tutoring sessions of students. The original creator designed the database for 1 semester. I am trying to make it functional for x number of semesters. So here's my dilemma: I have several reports that are based on queries. I would like for the user to be able to run the report and it prompt for them to enter a date range, which would then output desired results for that range (I thought this would be more efficient than setting up the db to run reports by semester, so that the user would be able to narrow their results).

So in corresponding queries, I am adding the following criteria to the "Date" field:
Between [Enter Beginning Date] And [Enter Ending Date]. The query produces the right results, just not exactly in the way I would like to see them. This is because the query tracks the number of hours per mentor (the person giving the tutoring) and does a sum.

So in my results, it is summing the hours for every change in date occurrence. For example, the results should look like this (on the report):

Student 1
Mentor A xtotal hours

Student 2
Mentor A xtotal hours
Mentor B xtotal hours

Instead what I'm getting is a sum of hours for every different date, as follows:

Student 1
Mentor A xtotal hours
Mentor A xtotal hours
Mentor A xtotal hours

Student 2
Mentor A xtotal hours
Mentor A xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours

And so on...

Maybe I'm approaching this is the wrong fashion? I haven't had much luck searching google or access help... maybe I'm searching with the wrong strings.

Any advice would be greatly appreciated.

I'm having a problem with queries, and I can't seem to find a solution in books - I looked through about ten of them and none of them addressed the problem. This may be because it has a painfully obvious solution...

A little background:
I am designing a database for a debt-collection law firm. One of the functions it must have is to keep track of various different sorts of financial transactions which can pertain to a given debtor (ie, a received payment, a cost expended, and a few other things).

The problem is that, in generating reports, I need to use queries to find several sums of only those transactions which fall into specific categories (for instance, to calculate the amount a debtor has paid against his balance, it needs to sum only those entries which are both linked to that debtor's ID number and whose type field reads "payment", and then subtract from that those entries whose type field reads "cost"). The problem is this: not all debtors may have "costs" entries, and when there are none, the report comes up blank with a single "#Error" written in the name field and nothing else present.

I believe the problem is that the Sum aggregate is returning a null value when the query finds nothing that meets the criteria. I have been unable to find a way around this; the Nz() and IIf() with IsNull() functions don't seem to be helping.

The query runs as intended when there are entries for every relevant type; however, it is undesired to have to enter a "payment" of $0, "cost" of $0 etc for every entry just so that this function works.

Is there anything I can do about this? Any input would be appreciated, as I'm fairly inexperienced with the use of Office Access. (If it matters, I am using Office 2003).

Hello Everyone. I'm trying to create an update query and have been failing. Here's the scenario - I have 2 tables: Order & OrderBreakdown. In my Order table I have a field named TotalCharge, the field I want to update. The OrderBreakdown table has InventoryID foreign key in it as well as a OrderID foreign key. The OrderBreakdown table is going to have multiple records, all with matching OrderID keys. In the OrderBreakdown table is a Subtotal field with the totals amounts for each record, I just need these all summed together (based on their OrderID's). But when I try to do this, access says "You tried to execute a query that does not include TotalCharge as an aggregate function". What am I missing here? I know what an aggregate function is, but Access won't let me choose the Totals button when I'm on an update query. The SQL i'm trying to use is below. Please help!!

UPDATE tbl_Order INNER JOIN tbl_OrderBreakdown ON tbl_Order.OrderID = tbl_OrderBreakdown.OrderID SET tbl_Order.TotalCharge = Sum([tbl_OrderBreakdown].[Subtotal])
WHERE (((tbl_Order.OrderID)=[forms]![frm_OrderBreakdown].[orderid]));

I have written some MS ACCESS 2003 VBA that allows a variable to be passed to a query, using a form to harvest criteria for the query.

Unfortunately, the variable does not seen to be passed on to the query, although the query function call is triggering the VBA - it just dosn't seem to be returning the value into the query. I have tested the rest of the code and the value I am trying to get into the query criteria is working up to the point of the query calling the function, the function code working, and then the criteria doesn't work in the query.

I have another function in the same query, set up the same way, and that one works fine for an integer value that I am using to return a unique event ID.

Thoughts appreciated.

Form code

' test variable when a button is pressed

strAttending = "Confirmed"
SetComboAttending strAttending

' code here to open query

Query call
' Function in the string field of interest

Module code
Option Compare Database
Option Explicit

'********** GLOBAL VARIABLES *********
Public strAttendingStatus As String

Public Sub SetComboAttending(Value As String)
strAttendingStatus = Value
End Sub

Public Function GetComboAttending()
GetComboAttending = strAttendingStatus
End Function


I wish to make a function but I do not make it and I require your assistance. I have a query with 76000 recordings and I have a field with a date. I wish when I launch my query to have all recordings of the previous day . We are thuesday on 03/18/03 and i would like the recordings for yesterday 03/17/03 but when the day is Monday, i would like all recordings for Friday and saturday.

I do this in my query but it doesn't work for Monday

SELECT MyDate, MyName, MyCountry, MyLastName FROM Rin WHERE MyDate Like Date()-1));

I do not see how to make that, could you help me.

Thank you for your assistance.


I have these statements which load the appropriate subform depending on the entry selected in a combo box.
The problem is that the function finds both strings to be the same and loads the wrong subform for the second statement. How can I make the function "see" the difference b't both statements?

ElseIf InStr(1, Brand, "Gourmet Supreme" ) Then
Me.childHigh.SourceObject = "subfrmColorsHigh_GourmetSup"
ElseIf InStr(1, Brand, "Gourmet Supreme Decaf") Then
Me.childHigh.SourceObject = "subfrmColorsHigh_GourmetSupDecaf"



I've managed to get myself confused (not hard to do). I am trying to create a SQL query with criteria taken from the current record in another recordset and open it. I get the error "Run time error '3061' Too few parameters. Expected 2." This occurs on the line

Set rst3 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Here's the function

Public Function RemoveOnhandIng()
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SortedShoppingList", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("Pantry Contents", dbOpenDynaset)
With rst1
End With

Dim listQuantity As String
Dim pantryQuantity As String

Dim mySearch As String
mySearch = "[Ingredient Num]=" & rst1![Ingredient Num]

Do While Not rst1.EOF

listQuantity = rst1!Quantity
If DLookup("[Ingredient Num]", "[Pantry Contents]", "[Ingredient Num] =" & rst1![Ingredient Num]) Then
'pantryQuantity = DLookup("[Quantity]", "[Pantry Contents]", "[Ingredient Num] =" & rst1![Ingredient Num])
With rst2
.FindNext mySearch
End With
pantryQuantity = rst2!Quantity
strSQL = "SELECT ConversionFactor FROM MeasurementConversions WHERE FromUnit = '" & rst1![Unit Num] & "' AND ToUnit = '" & rst2![Unit Num] & "'"

Set rst3 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

If Not (rst3.EOF) Then
End If

If rst3.RecordCount 0 Then

If (rst1![Unit Num] rst2![Unit Num]) Then
listQuantity = ConvertUnits(rst1![Unit Num], rst2![Unit Num], rst1!Quantity)
End If
End If

End Function


I have written a macro that will run through a set of queries and export the results to Excel.

I want to "copy and paste" this set of queries, but to insert a statement before the macro reruns all the queries to change one of the criteria in the queries.

In other words, Field1 is binary and may be either 0 or 1. I want the macro to run the queries with the criteria for Field1 = 0, then to run the same queries, on the same data but all with Field1 = 1.

I have tried the "setvalue" function, by opening the relevant query and then trying to change the criteria from 0 to 1 or vice versa, but this doesn't seem to work. It may be that I am not specifiying the object correctly, but I don't know how to correct it. Specifically Access tells me "MOA can't find the name 'queryname' you entered in the expression"



I am trying to create an export function in Access to export a query with filter that is controlled by a combo box then export the query to Excel.

So far I browsed this forum and other sites and put together the VBA code that was used by other to try and accompolish what I want.

Private Sub ExportExcel_Click()
Dim StrSQL As String
Dim qdf As QueryDef

StrSQL = "Select * From [ExportQuery] Where [Run.Test_Case]= Forms![Export]![ComboExportTestCase]"
If DLookup("Name", "MSysObjects", "Name= 'ExportQuery'")  "" Then
    Set qdf = CurrentDb.QueryDefs("ExportQuery")
    qdf.SQL = StrSQL
    Set qdf = CurrentDb.CreateQueryDef("ExportQuery", StrSQL)
End If
DoCmd.OutputTo acQuery, "ExportQuery", "MicrosoftExcel(*.xls)", "", True, ""

'DoCmd.TransferSpreadsheet acExport, 8, "ExportQuery", "ChecklistExport", True, ""

End Sub

After I select it from the form and click the button i get a runtime error 3270 Property Not Found. I am not sure where in the line of code its refering too???
But the compilier points to the docmd line:

DoCmd.OutputTo acQuery, "ExportQuery", "MicrosoftExcel(*.xls)", "", True, ""

If anyone has tried this and has seen the error I'd be pleased to see how it is resolved.
I also zipped up a copy of the DB with some mock data so you can see it.


Hi All,

Can anyone help me out.

I am trying to to link a Access Query with an Excel Sheet. I have tried the Paste Special function, but everytime I open the excel sheet it does not pull the latest data although I click on update data.

Hi everyone,

I have a requirement to the one similar to the functions CONNECT BY PRIOR, START WITH in oracle. I have table fnd_menu_entries_vl which contains menu_id and sub_menu_id which have a parent and child relationship.

My requirement is to ultimately return data with menu_id, entry_sequence, sub_menu_id, function_id arranged in the order of the parent > child as a tree as generated by the below query in oracle database. In one way this can be achieved simply by using CONNECT BY PRIOR, START WITH functions in oracle.
WHERE menu_id IN
SELECT sub_menu_id
START WITH menu_id = 69402
CONNECT BY PRIOR sub_menu_id = menu_id)

taking an example from the query - the below is the tree - Top level parent 69402 > 68672 > 67499 > 67500 > 68296 > 67913

Also attaching the data in a xls file with the table columns and data exported from Access database 2007. I have done a lot of search regarding this, but I didnt find any similar posts.

Appreciate any help in this matter if anyone has developed similar function resulting in a Hierarchy Query with parent and child relationships.


I like to make a query for extracting latest (max) rev of one list like below. But for catching this result I have made 3 queries

that are related
(one by one). So I have floated between queries with slow speed.


	DocumentNo    title    REV    DATE        TRANS
DW-1013        test    0    12-Dec-09    TT-3799
DW-1013        test    1    13-May-10    TT-5923
DW-1013        test    1    15-Apr-11    TT-5943
DW-1013        test    2    1-Jan-12    TT-6031


	DocumentNo    TITLE    REV    DATE        TRANS
DW-1013        TEST    2    1-Jan-12        TT-6031

For this result if have made 3 queries that are related.

1- First query for finding max rev of the list.(group and aggregate function)

2- Second query for finding max date that has been made with relating first query and list with joining (doc no, rev)

3- Third query for finding max transmittal , that has been made with relating second query and list .

So slow of third query that has my result is slow (some times around 17 second) , please help how I can make better query.

Best regards.

I have an issue where my Exists function in my Append query keeps giving me this error message "AggregateType is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not to long."

Here is the exists line.

Test: Exists (Select * from [SDSK Leave Taken] as e WHERE 
[Tmp CL].[IBB Date] = [e].[IBB Date] AND 
[Tmp CL].[Charge Date] = [e].[Charge Date] AND 
[Shops].[ID] = [e].[Home Shop ID] AND 
[Shops_1].[ID] = [e].[Borrow Shop ID] AND 
[tmpReference Personnel].[ID] = [e].[Badge ID] AND 
[SDSK Charge Types].[ID] = [e].[Charge Type ID] AND 
[Tmp CL].[Work Hour] = [e].[Charged Time])

If anyone can please help me out I would really appreciate it.

Almost forgot the reason I am using the exists function is to make sure the exact record does not exists and if it does not then insert it otherwise skip it because I have the criteria for the field set to false.


Not finding an answer? Try a Google search.