Wildcard Query With User Input Parameter Not Working
Jul 12, 2007
I'm using an Excel spreadsheet that is importing external data from an access database in which I've got a field where the name of a person checking materials out is entered. it is currently set up, and I cannot change it, as a free form field. So folks enter information in a variety of ways.
For example, Larry Martin might be entered as "Larry Martin" or "Martin, Larry" or "larry.martin@somewhere.com". I'm trying to run a query that would look in that field for any entry with the string I enter, such as "Martin."
I've tried setting the criteria like this:
Like "*" & [Which Last Name] & "*"
However, when I try and run the query I get a message telling me the system is expecting two parameters.
Does anyone have any idea what I'm doing wrong? I've been banging my head against this for awhile now and am thoroughly stumped! All assistance, as usual, is greatly appreciated!
View Replies
ADVERTISEMENT
Oct 14, 2013
I have created a query with the parameter for the Domain field. however on the form the user enters this information via a drop down menu. i was just wondering could the parameter box be set to a drop down box as well to save the user entering in the full Domain field name?
View 6 Replies
View Related
Feb 28, 2008
hi,
new to the forum so hello everybody,
what i have is a table of contacts. this table has a field called locations. i have created a queriy that askes me the location which works ok. i could also have the criteria that has like "lo*" in it
but what i really need it the query to run ask me the location and i only put in LO* OR BI* etc
and the results i get are either london or birmingham etc
thanks for you help
steve
View 2 Replies
View Related
Mar 28, 2007
I know I have seen post close to this one but not hitting what I need.
I currently have a query that is structured as such...
Service: IIf([Serv] Like '*AA*',' Bas ','') & IIf([serv] Like '*AB*','Exp ','') & IIf([serv] Like '*CA*',' Dig ','') & IIf([serv] Like '*89*',' 384 ','') & IIf([serv] Like '*8H*',' 3M ','') & IIf([serv] Like '*?5*',' 5M ','') & IIf([serv] Like '*=W*',' 10M ','') & IIf([serv] Like '*<1*',' Tele ','')
Works great except for the last IIF stmt has now been changed to look for the code *6 - not wildcard6 -
How can I get Access to realize that I need it to look for
anything to the left or right but it must find the *6
This IIf([serv] Like '**6*',' Tele ','') wont work b/c I have other codes that end in 6.
I am at a loss - any help is appreciated.
View 2 Replies
View Related
Sep 24, 2006
Hey Everyone, This one is driving me crazy. I have a query that is based on 2 tables. I need the result of the query to be the sum of hours or if no hours then I need a 0. I have searched and tried just about every IIF and NZ function I could find and when I add them I get no results. Which then gives me #error on my report. I have attached a pic of my query and results. I would be forever thankful if someone could help we with this as I have been struggling for a week on this issue and my boss is getting on my case about why this is not done.
Thank you in advance for all your help with this.
View 2 Replies
View Related
Jul 23, 2006
I am not a programmer and I don't know VBA/VB.
I am using this as a workaround to avoid VBA functions (since I don't know them).
I can't seem to find a simple? solution to this.
I have a query that does a radial search in decimal degrees. I have created an unbound form as a dialogue box that converts degrees-minutes-seconds to decimal degrees and displays the results in a calculated text box. What I want to do is have the query take the results in the calculated text box as its parameters (along with a third parameter- distance) without prompting the user. How do I get the query to take its results from the fields on the form? I have tried Like [Forms]![frmName]![SearchValue], but I can't seem to make it work.
View 2 Replies
View Related
Apr 28, 2006
I have a report that has an underlying query that asks for Start Date and End Date.
Is there anyway that I can get what the user inputs into the box to be put into the Page header of the report?
ie: user enters into the parameters
[Start Date] 01/01/2006
[End Date] 04/04/2006
Then when the report displays it says
Report for the period 01/01/2006 to 04/04/2006
Thank you for your time
View 4 Replies
View Related
Jul 15, 2014
I have a table which holds information on audits that have been carried out on staff member's actions. The 'Supervisor' field is populated via a combo box which is linked to a separate table (tblSupervisors).
I am now trying to build a query to allow me to extract all audits that have been carried out on a specific supervisor - rather than the criteria to be [Enter Supervisor Name] and allowing text entry, I thought it would be better to have form that pops up with a combo box that is used to select the supervisor (from tblSupervisors);
So far:
- Form "Supervisor_Select" is created, and has a combo box that looks up from tblSupervisors
- Macros as specified in the instructions are created (Open Dialog, Close Dialog, OK and Cancel)
- Query is done, all bar the criteria expression on the desired field.
- Module is created as described in the instructions, and is called "Supervisor_Select"
I have tried putting the following in the criteria;
[Forms]![Supervisor_Select]![cboSupervisor], however I think I am missing the bit where the query opens the "Supervisor_Select" form?? Will this only work from a button where the on click event runs the 'Open Dialog' macro and then runs the query?
View 4 Replies
View Related
Jan 30, 2007
I have data recorded using an input mask,
"RF">L-0000;;*
to display data in the style: RFA-0001, RFA-0002 etc.
I have a make table query to join this field to another, to create a combo box look up.
Unfortunately, after I run the query, the only data from the input mask that gets imported is A0001, A0002 etc.
What have I done wrong? I guess the error must be in my specification for the input mask...
Any and all help gratefully received.
View 4 Replies
View Related
Jul 31, 2014
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
View 3 Replies
View Related
Oct 10, 2006
I want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?
View 14 Replies
View Related
Mar 15, 2007
Hi all.
Stumbled onto this forum during a google search.. looks like a great forum.
I have a question.
this thread http://www.access-programmers.co.uk/forums/showthread.php?t=124689
goes into a simple function to group by the name and add their total.. however what i need to do is that plus have a user input of the date.
eg of the table
Date Title Invoice
02/02/07 ABC123 11.00
02/02/07 ABC123 12.50
02/02/07 DEC123 11.50
03/02/07 ABC123 10.50
What i need is a paramater query to be able to work to give me the total of the groups..
Whenever i try to join my Parameter query with my sum query it gets messed up..
heres the code... what am i doing wrong?..
What i want displayed after they input the date is the group by and sum total of the parameter query..
SELECT RawData_tbl.[Title 2], RawData_tbl.[Estimated Value], RawData_tbl.[Date In], RawData_tbl.Quote, RawData_tbl.GST, Sum(RawData_tbl.Invoice) AS SumOfInvoice
FROM RawData_tbl
GROUP BY RawData_tbl.[Title 2], RawData_tbl.[Estimated Value], RawData_tbl.[Date In], RawData_tbl.Quote, RawData_tbl.GST
HAVING (((RawData_tbl.[Date In]) Between [Please Type the first day of the month] And [ Please insert the last day of the month]))
ORDER BY RawData_tbl.[Title 2];
View 5 Replies
View Related
Apr 14, 2008
Okay my access skills are low at best and I'm trying to build a database for work so hopefully this isn't to stupid of a question.
I'm trying to creat a query that will allow the user to type in a work type and have it generate the all records containing that work type. The way the data is in there to get it to generate just a specific work type without the input I have to use Like "*newacct*" for example. Is there any way to do this since there are more then one work types for some records?
Thank you
View 8 Replies
View Related
Jun 5, 2006
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
View 1 Replies
View Related
Aug 20, 2013
I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.
Code:
Option Compare Database
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
[code]....
The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.
Code:
Beep
strBegindatemsg = "Enter the beginning date." & vbCrLf & vbLf
strBegindate = InputBox(Prompt:=strBegindatemsg, Title:="Begin Date")
Beep
strEnddatemsg = "Enter the beginning date." & vbCrLf & vbLf
strEnddate = InputBox(Prompt:=strEnddatemsg, Title:="End Date")
Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.
The following is the SQL for my union query.
Code:
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category FROM qry_PP_UIC_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids" FROM qry_PP_Bid_Error
[Code] ....
Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.
View 3 Replies
View Related
Sep 10, 2013
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
View 1 Replies
View Related
Jul 29, 2013
I have a cross tab query that takes input from the user. The code is as under:
PARAMETERS [START_MONTH] DateTime, [END_MONTH] DateTime;
TRANSFORM Round(Sum(([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM
SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block=PRODUCTION.FIELD_BLOCK
WHERE ((PRODUCTION.MONTH) Between Format([START_MONTH],"dd-mm-yyyy") And Format([END_MONTH],"dd-mm-yyyy")))
GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
PIVOT PRODUCTION.ACTIVITY;
I am facing 3 problems with the above code:
1. Access prompts to enter the start month and end month even when I click the save button.
2. When I run the query it prompts to enter the start month and end month twice.
3. I have generated a report based on the above query. When I run the report it keeps on prompting for start month and end month and does not give the final result.
Is it a common problem with cross tab queries?
View 5 Replies
View Related
May 26, 2015
I have a crosstab query. users need to run the query for any week of a year. It is always a YTD query. I have used this process on many select queries. Will a crosstab query not support this simple method? See attached screen shots.
View 2 Replies
View Related
Nov 3, 2005
Ok this might be basic stuff i'm a bit dumb
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
Please help the dumb
View 3 Replies
View Related
May 22, 2013
I am building a form in access and I am trying to find a way where user input isn't possible in the associated textbox when "No" from on option box is selected.
View 3 Replies
View Related
Oct 8, 2014
Is it possible to have the user select which field is added to the query through a parameter? In my query I have a table where each record has a part number and has sales number for each month. When the user runs the query I want them to be able to select which month is returned.
My table basically looks like this:
PartNumber,Jan,Feb,Mar,Apr,May etc.
01PartNumber,15,20,30,25,28 etc.
02PartNumber,12,14,12,16,20 etc
I want the user to enter "Mar" when prompted and return the parts numbers with values from "Mar".
Access 2007
View 10 Replies
View Related
Dec 12, 2014
I have a report that is fed by a parameter query. When a command button (Command23) is clicked a parameter box opens and the user enters a subsystem number. A report for that subsystem then opens.
I want to open the report with VBA and show a message if the user has failed to enter a subsystem number in the parameter box and just clicked OK.
The problem is I don't know how to refer to the parameter box and detect if it has been left empty before clicking OK. I first tried to use IsNull in an If statement with Command23.Value, but now realize it is the value in the parameter box I am after, not the "value" of Command23.
How can I change my code to detect if the parameter box has not been filled out?
Private Sub Command23_Click()
If IsNull(Command23.Value) Then
MsgBox "You did not enter a Subsystem", vbOKOnly, "No Criteria Entered"
Else
DoCmd.OpenReport "rptTESTInfoBySubsystem", acViewReport
End If
End Sub
View 1 Replies
View Related
Sep 17, 2013
I have an append query that currently looks like this URL...This query automatically adds the machine parameters for a product code and lot number into the running condition log. This is so the user does not have to manually go in and tediously select each machine parameter.
The running condition log also has a date field to specify what day and record number the machine parameter's value was recorded on. When I run the query the appended rows look like this. The product and lot are defined by user parameter and there are actually about 36 machine parameters
Code:
Productstockcode LotNo Day Record Parameter ActualCond
PE-500 130816m71 StockTemp
PE-500 130816m71 Zone 1
PE-500 130816m71 Zone 2
My question is: how do I modify the query to automatically add the date and record number in one shot? It needs to be user defined at the time of the query because this data is not stored anywhere else in the database. For each 36 machine parameters the day and record would be the same.
View 3 Replies
View Related
Oct 27, 2005
Hi:
In MS Access:
In the query:
I set a input parameter: [Please enter the date:]
I run it, enter a parameter. The results come out, it is correct.
And then I sort the ascending order of the Vendor Name.
And close the query.
Second time, I run it, It give out 2 times [Please enter the date:]
I need to enter twice input. Why?
How can I solve it?
Please let me know, thanks.
View 1 Replies
View Related
Nov 20, 2007
Hello,
I have two tables: COSTING and PRICING, both with the following fields:
product name French
packaging
transportation
ddp
I want to create a query so that from a text box in the pricing form the user could enter a desired "margin" (ie. 10%) and the query would replace everything in the pricing table with the data from the costing table multiplied times the margin entered in the text box.
how can i accomplish this???
so far i can update the pricing table with the costing data with a action (update) query, but im still missing how to integrate the user "margin" input. At the same time i have the problem that the query limits the update only to the products that are ALREADY in the pricing table. So it doesn't copy ALL the products from the Costing table.
Thanks,
Gilberto
View 2 Replies
View Related
Feb 7, 2006
Hi to all
I am setting up a query to report on the turnaround time for appication to be processed.
I have a calulated column to calulate the numbe rof days betwen receipt and approval of applications. I now want to be able to run the query and subsequently the report by having a prompt to enter anlower and upper number of days range.
I have used the experssion Between [Lower range] and [ Upper range] to prompt and when I enter say 1 to 5 days the query returns all days.
Any thoughts as to why it is not working and what can I do differnetly?
Regards
mgerada :(
View 6 Replies
View Related
