Query With Varying Number Of Parameters
May 19, 2006
Greetings,
I am trying to write a parameter query ... I have multiple fields the users *could* search, but they may want to search only one, and I am having a struggle figuring this out.
Ex. fields to search are Gender, Age, Date, etc., and I have created a parameter query that includes parameters for each of these fields. However, if I want to see all the females, thus leaving the other two parameters blank, I have no records in my result set. Clearly, I am doing something wrong -- I have searched the forum extensively before posting.
Thanks in advance, I am sure I am being simple and missing somethign....
View Replies
ADVERTISEMENT
Jun 23, 2015
I am designing a data-entry system in Access 2003 for a clinical case-reading study. The questions that the case readers have to answer are divided into modules, and different batches of records use different modules, but all batches use a core module which includes identifying information and a set of questions that apply to all cases. The modules can include anywhere from about 15 to 60 questions. Some of the modules have been written, but the idea is to create new modules as the need arises. The data will be exported to an Excel spreadsheet for analysis. There is no need to plan for reports and this isn't really a relational database.
The problem is to allow the reader to enter data for the relevant modules, sometimes one, sometimes four or five or more.
I have created a main table for the core module and a form to go with it. I’m assuming I need to create a separate table, related to the main table, for each module and an accompanying subform for each table.
How do I create a menu system that will allow the case reader to choose only the subforms needed for a particular record?
View 4 Replies
View Related
Aug 16, 2014
I made a query that requires to enter two parameters (both are Number data type). I need the 2 parameters to be a criteria in the same row (AND). When I run the query it gives me zero result (and the data is already exists in the table). What is the mistake?
View 10 Replies
View Related
Feb 6, 2014
I have a contract management database where contracts have a start date and an end date. I also have a date parameter set up whereby I can show live contracts between overlapping records.
I want to be able to count the number of days for each contract that is live between the dates of the parameters.
For example my contract might run from 1st to 30th November but I might want to report from 10th October to 10th November so the number of days I need the query to return is 10 days as my contract is not live before the 1st November. Or my query might run from 1st October to 31st December, then I would need it to show 30 days as it covers the whole of this contract.
View 3 Replies
View Related
Dec 4, 2007
I have a query that I export to a text file with fixed width settings.
For one particular field that had only been one character and 5 spaces I used PGM: " " & [ProgramCode] for my field. The recipient of the export file requires that this field be 6 spaces long and leading spaces fill in any unused field.
Now, one of the records for this field will be 2 character so the way it is now will cut off the leading character. How can I write this to add leading spaces until it reaches 6?
View 1 Replies
View Related
Feb 6, 2006
Hello,
One of the items on my form has a value of varying length (decimals). My query is picking up the complete value but the field displays only 2 decimals. The field property is set to
Format = ##,##0.00###
Decimal = Auto
If i click on the field, it shows me the correct value e.g. 0.0001
How can i display the complete value on the form.
Thank you.
View 3 Replies
View Related
Sep 10, 2007
Hello,
I'm having a hard time trying to figure out how I should go about designing the table structure for the hours tracking for the database I'm working on. Basically, I have a table with projects (tblProj) that I need to track the number of labor hours worked per month. The problem lies in the fact that each project could have varying time periods, anywhere from less than a year to more than a year and can start and end at any time of the year.
I was thinking of creating a second table (tblProjHours) to store the hours for the project but I'm not sure what the most efficient way of doing this would be. My idea was to create a table as follows:
tblProjHours
ProjHoursID (pk)
ProjID (fk)
BeginDate
EndDate
Month1
Month2
Month3, etc....
I would create a large number of fields/columns, something like Month1 to Month36 just to make sure I have enough months to enter in the hours. This is of course inefficient since some projects would be way shorter than the maximum allowed months set by the table structure and there is always the possibility of surpassing the maximum allowed months based on the table structure. I was wondering if someone had any better ideas on how I should pursue the design. Thanks in advance!
TheMach
View 2 Replies
View Related
Dec 30, 2014
Preamble edit: I'm running Access 2003.
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
View 3 Replies
View Related
Aug 2, 2015
I create several reports for several different companies. Report data is the same but the report header data changes based on the company selected. All works well until I attempted to add their individual logos. I have tried many of the suggested methods both here and elsewhere to set the image path in Image.picture and image.control source. But I get "windows can't open file". Access 2010 on Windows10 insists on using the insert picture window when i select an image control. There is a drop down in the property sheet. I've gotten it to work a couple times but then its gone after restart.
The header data comes from a table [Company] which has the fields - name, addr, phone, path to image, and active(yes/no)
View 3 Replies
View Related
Jul 8, 2013
I am working on a report that will have 16 sub-reports, one right below the other. Because of the complex nature of the calculations in the groupings it seemed easier to "build" the report using vba rather than using the report's built-in grouping ability. The issue I am running into is that there seems to be no simple way to control the height of the sub-reports. I had hoped that setting "can grow" to yes would change the height of the sub reports and move all subsequent sub reports down (Allas)
As an alternative I thought too that I might be able to set the height of the sub-report control, which would make for a tricky but not impossible bit of programming, but (Again allas) I can't seem to find a reasonable way to determine the required height of the sub-report's control.
View 3 Replies
View Related
Aug 22, 2012
Using MS 2007, I have a 200 text files exported each day from another application that has two different types of lines (see below). I would like to import each text file in to a database as a single record.
Text file example (text.txt):
R111 WC 8/21/2012 7:00 Doe, John doej 10110110
First Question? Y
Second Question? N
Third Question? Y
...
Seventeenth Question? 10
As you see, I have the first row with multiple fields, but the next rows I have a question and an answer.
I would like to have this data imported as shown in the attachment. Example.zip
Most answers I see are for either multiple lines (same data and sizing). I am not sure how to handle several different lines with that vary in size and delimiters.
View 5 Replies
View Related
Aug 30, 2004
I have a parameter query with 5 possible user entries.
The criteria for all my fields is set up as follows:
Criteria: IIf(IsNull([Enter County]), "", [Enter County])
Or: Like "*" & [Enter County] & "*"
If the user enters something for 2 or more parameters it seems to work, but if they just enter a county, for example,, it gives an error message about the expression being too complex.
Any suggestions.
Thanks.
View 9 Replies
View Related
Jan 4, 2006
I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...
strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"
Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.
SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;
Your help will be greatly appericated.
View 3 Replies
View Related
Feb 1, 2006
Guys I need your help/Advice...
In my Access Database I have a query (lets say qry1) and in this query i have 2 fields for start and end date, which is provided by 2 Get functions.
also i have qry2 based on qry1
then qry3 based on qry2
and finally qry4(using sql in code) based on qry3, and non of these 3 queries have the start and end date fields.
now here is the problem: I am trying to set the criteria in qry4 and then open a record set on this query(qry4) to use the data that it pulls out...
strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strsql)
but when the last line is executed I get this error message:
Runtime Error '3061':
Too few parameters, expected 2.
i also tried doing this:
strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
MyDB.QueryDefs("qry4").sql = strsql
Set rst = MyDB.OpenRecordset("qry4")
but when the last line is executed it gives me an error message saying that the query does not exist or the name is not spelled correctly. (Ps. I have created the query and the criteria does update once the Select statement is run in the code!)
again the reason for this is that the query has not been populated as the main query (qry1) needs 'strat date' and 'end date'!
Is there anyway I can pass these 2 parameters to qry4 directly using code? If there is a way then this will definitly work as i tried opening the query manually in the Query window and after I input the 2 dates in the input box the query ran successfully!
I would appreciate any help/suggestion guys, I need to sort this out quickly as i have a deadline... Cheers
View 1 Replies
View Related
Sep 13, 2006
I’ve the following query definition “selOrders”
PARAMETERS [DateFrom] DateTime, [DateTo] DateTime;
SELECT * FROM Orders WHERE OrderDate BETWEEN [DateFrom] AND [DateTo]
I want to open this query as a DAO.Recordset but have problems to assign values to the parameters. I tried different possibilities but invain. My latest try was as follows:
Dim QryDef As QueryDef
Dim Date1, Date2 As Date
Dim Orders As DAO.Recordset
Set QryDef = CurrentDb.QueryDefs("selOrders")
QryDef.Parameters("DateFrom") = Date1
QryDef.Parameters("DateTo") = Date2
Set Orders = CurrentDb.OpenRecordset("selOrders")
During execution error 3061 (Too few parameters, expected: 2) occurs.
View 1 Replies
View Related
Apr 10, 2007
Hey,
I am trying to enter a user-defined parameter in a CrossTab query with little luck. It works fine if I enter the code already defined as in: >= #12/06/2006# but when I enter the code for a user-defined / input such as: >= [Enter date:], or if I try to redirect to a textbox on the active Form such as: >=[Me]![dfrom.Value] I get the error "Invalid field or Expression), although this syntax / code works fine if I do this in a normal query.
Any suggestions?
Kind regards, Adam.
View 2 Replies
View Related
Sep 11, 2006
I have created a form with multi-select list boxes, behind which is the following code to transform the users' selections into query parameters.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("Test")
If Me!lstAB.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstAB.ItemsSelected
strCriteria = strCriteria & "Centres.[Area Board] = " & Chr(34) _
& Me!lstAB.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Centres.[Area Board] Like '*'"
End If
strSQL = "SELECT * FROM Centres " & _
"Where " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "Test"
Set db = Nothing
Set qdf = Nothing
End Sub
I would like to allow the user to set, on the same form, which field they want the resultant data to be sorted by, preferably by having a tick box alongside each list box, which sets that field as the sort field. The form will obviously need to allow only one of these tick boxes to be selected.
Can anyone advise me on how to modify the above code to make this possible?
Thanks,
Gary
View 1 Replies
View Related
Oct 1, 2006
Hey guys, I have a simple design question I hope someone can help me with. I have little to no experience with Access, but I know enough to get around. Anyways, I'm working as an extern for a rock band. One of my assignments is to create a publicity contact database. I have all my forms and tables set up but I'm having some trouble creating my query. There are about 10 fields in the contact table. I want the user to be able to search any one of those 10 fields OR any combination of them. Is there a way to do this with only one query, where it willl prompt the user for only the fields they have selected to search?
View 2 Replies
View Related
Dec 1, 2013
I have a query that pulls information from two tables. Some of the fields that are being queried share the same name in the tables, [Reimbursed_Amount] and [Cancel_Fee] specifically. In Design View I have specified that I only want the query to pull these fields from the Event Information table. An error occurs when I try to run it, saying that I need to define which table the field is from in the SQL code.
But then after I added clarification in the SQL, when I run the query it now prompts for a parameter for each of these fields. Why is this happening? I leave it blank, so a parameter has no impact on the query. How can I stop this?
Here's the SQL, after I added the table clarification:
Code:
SELECT (Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee].[Event Information])-nz([Reimbursed_Amount].[Event Information]))) AS Total_Cost, [Shared Billing Information].Paid, [Shared Billing Information].Shared_Billing_ID, [Event Information].Shared_Billing_ID
FROM [Shared Billing Information] RIGHT JOIN [Event Information] ON [Shared Billing Information].Shared_Billing_ID = [Event Information].Shared_Billing_ID
[Code] ....
View 7 Replies
View Related
Jul 26, 2012
I have a report and export function which is based on date parameters. The field name called "date". However, I added a new field called "followup-date" because we would like to track customers that are returning back. Now, my problem is when I go to Export by date parameters or generate the report by certain date eg. July1st to July 28th, it only pulls up the record based on DATE field.
Is it possible for the date parameters to look up july1st to july 28th under date field as well as followup-date and pull up those records that are meet the date parameters? The reason I have to do this is because some customers do not come back for followup so I have to look under 'date' field too. While some customers can have more than one followup.
View 2 Replies
View Related
Oct 31, 2005
I am trying to run the below union query, but it keeps asking for FRGHT_BL!FB_CREAT_DTM as a parameter. I want it to only ask for the Start Date and End Date once.
SELECT * FROM qLOC_ID WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION SELECT * FROM qLOC_ID2 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION Select * From qLOC_ID3 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date]));
View 1 Replies
View Related
Aug 19, 2007
Hi There,
I am trying to get a query to pull dates from a form to use as limiting parameters for a report. One of my forms has the user enter a range of dates into two text boxes (a from-date in one box and a to-date in another) as the range of dates they want to pull information from a table via the query. These dates are also used in the Title header of the report that is generated from the query information.
How can I get the query to use these dates in the text boxes, rather than having to use the "between / and" command on the criteria line of the query design?
If I just have parameter windows pop up and have the user enter the dates there, I can't use those dates for the report, or can I? Any help would be appreciated. Maybe I haven't explained this properly, if so, I'll try again if I've just confused you.
View 3 Replies
View Related
Oct 5, 2007
Hello..
I have a query that has multipal parameters for that query. I want to be able to set it up so that the person doesn't have to use all the parameters.. IE then can use just the dates, or just the test type etc...
Here is the sql that I am currently using:
SELECT tblApplicant.ApplicantID, tblApplicant.ApplicantLName, tblApplicant.ApplicantFName, tblApplicant.LastFour, tblApplicant.TestDate, tblApplicant.JacketExpireDate, tblApplicant.Comment, tblApplicant.HomePhone, tblApplicant.CellPhone, tblApplicant.WorkPhone, tblApplicant.ContinueProcessDate, [ApplicantLName] & ", " & [ApplicantFName] AS Name, tblYsNo.YesNoType, tblLuTestType.Type
FROM tblYsNo INNER JOIN ((tblApplicant INNER JOIN tblLuTestType ON tblApplicant.TestType = tblLuTestType.TestType) INNER JOIN tblCaller ON tblApplicant.ApplicantID = tblCaller.ApplicantID) ON tblYsNo.YesNoMain = tblApplicant.Interested
WHERE (((tblApplicant.ApplicantID)=[Forms]![frmMainData]![ApplicantID]) AND ((tblApplicant.JacketExpireDate) Between [Forms]![frmReport]![txtStartInt] And [Forms]![frmReport]![txtEndInt]))
ORDER BY tblApplicant.ApplicantLName;
Thanks
View 7 Replies
View Related
Aug 10, 2006
My database contains information about events running in different areas. I want to make the filtering options for this information as flexible as possible and so want a query where most of the fields can have criteria set, but where they can optionally be left blank and so the data will not be filtered on that field. To do this, I am trying to create form which collects together the parameters required by the user which the query can then pick up.
I want to use list boxes which contain all the entries for a particular field, looked up from the original table (e.g. all the different town names from the town field of all the records). This will allow the user to select the one(s) they want to filter for.
However, using list boxes presents me with the following problems, with which I would appreciate help:
1) How do I refer to a list box from a query and get the correct code created? I could do this where I'm just refering to a text box, but then a text box only contains one value. How do I tell Access to look for any of the towns selected in a list box?
2) There will be options on my form to filter the data by any of the fields of which it is made up. Obviously, the user will not want to filter every field and so I need a way to tell Access that if the user has not made a selection for a particular field, then it should not filter that field when the query runs.
If anyone can help me with these two things I would greatly appreciate it. I've so nearly finished developing a new system and this is the last hurdle I need to overcome!
Thanks in advance,
Gary
View 1 Replies
View Related
Jan 22, 2015
This is my first time writing a pass through query pinging sql tables using an input parameter from a form. I have gotten as far as executing the query but I can't seem to display the result to ensure it's pulling the right records. I also want to be able to append the records to a table. Below is the code I have written so far:
Sub GETRT()
Code:
Dim db As DAO.Database
Dim QDF As QueryDef
Dim STRSQL As String
Dim RS As Recordset
STRSQL = "SELECT * FROM LAB_MESR.ODM_RT_DAYS" & _
"WHERE LOCATION_ID=" & [Forms]![PARMS]![STR_NBR]
Set db = CurrentDb
Set QDF = db.QueryDefs("001:GET_LT")
QDF.SQL = STRSQL
End Sub
View 5 Replies
View Related
Aug 15, 2014
Do access VBA implements parameters passed to query strings in all following parameters?I've been working in ASP.NET/Razor C# and this would be an example of how it would be done:
Code:
db.Query("INSERT INTO threads (name, date_of_creation, user_id, area_id, user_group_id)" +
" VALUES(@0, @1, @2, @3, @4)",
Request["txtThreadTitle"],
DateTime.Now,
Session["user_id"],
area_id,
0
);
View 6 Replies
View Related
