I have also tried this search form from You Tube. It works fine however it doesn't pull up all my records.
I was told I need to add some IsNull code to it. Would you happen to know how to do that to the criteria?
So I have a query which prompts the user for a username to search by, GetUsername.
Im used to TSQL usage which would be something like:
exec GetUsername 'blah'
My goal is to generate about 10 reports based on this query but with different (hardcoded) input. I do not want to prompt the user while generating these reports.
How would I invoke this query with a hardcoded parameter? (Id rather not save 10-20 versions of it).
hi everyone!
Is it possible to write a datashaping statement as a stored query?
I tried but it didnt work..and I dont want to write the sql statement in my asp page..
can anyone help me with this???
thanks
I have a parameterized query to which date needs to be passed from a text box on the form. Also after running the query, the output of the query should be transfered to an Excel Sheet. Do I need to macro ? How can I achieve the result with a macro ? Or is there any other method ? Please assist.
View 5 Replies View RelatedHi,
I have a several parameterized queries that I would like to run using the same information, but don't want to make the user enter the same parameters over and over again. What I envision is the use clicking a button to run a macro that will run each query and then open a report with the results of the query. This report will the output from for queries that require the user to enter the start and end date. Is there anyway, for me to have the user enter those dates one time and for each query to call that same information? I was thinking I could have the user enter the start and stop date into unbound fields on a form and then click the button, but I don't know how to call that information into the query parameter box. Any suggestions/examples would be appreciated. Thanks in advance for your assistance.
Cheers,
Peter
I've seen a common solution is to double up the apostrophe, replacing ' with " to avoid the special character however i also hear of another solution using "parameters".I tried have a look around on the internet and got confused so I thought maybe it would be better to ask here.
I just want to change my INSERT INTO and UPDATE commands to be able to use this (to prevent a potential sql injection) also i think its important to know im using DOA to code? (not sure if its important) either way.
Can't seem to locate a similar situation in the forum for this problem. I have a form based on "qryProjects". I have another form "Menu" which has cboType, lstStatus and lstSubtype. I thought that I could select criteria from each and, with a command button, have the form open up filtered with the three criteria. In qryProjects pane I have as the criteria for each of the three fields:
[Forms]![Menu].[lstStatus] - Value list
Bound column = 1
[Forms]![Menu].[cboType]
SQL in cboType = SELECT DISTINCT [SubType].[Type] FROM SubType;
Bound column = 1
[Forms]![Menu].[lstSubtype] = this is filtered on the After Update of cboType
SELECT [SubTypeID], [Subtype] FROM Subtype;
Bound column = 1
I have them on separate lines in the query. When I run the query, it only filters on cboType and not the others. If I place the criteria all on the same criteria line, I get nothing.
Is my criteria in the wrong place? I was originally trying to make the form completely "unbound" and build a SQL string to pull the records but don't know if that is possible. My purpose is to eliminate macros that I was using as it took over 2 minutes to open the form with the BE residing on a shared network drive.
Would appreciate any ideas/suggestions.
Thanks,
Toni
so I have been working on this form for a while and I'm really a newb at this
maybe it easy maybe its not but i just couldn't figure this out
I'm so frustratated that I'm willing to pay(beer money for help)
well anyways I have this form
http://i27.photobucket.com/albums/c176/rlan214/WorkAssignForm1.jpg
at the side you can see that there are 3 filters
1st has check box (check box is always checked) for the date
2nd check box for Target Location
3rd check box for ForkliftID1
I also don't know how to change the date format in my date
its in yyyymmdd format right now and I want it mmddyyyy
In the middle theres a blank, and thats where I wanted my
query to show
here's my query
http://i27.photobucket.com/albums/c176/rlan214/WorkAssignQry1.jpg
If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful
Jim
If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful
Jim
I have a query form that allows user to search by two criteria (in which, user can select "And" or "Or" clause for the two criteria). I also allow user to filter the results by date range. On the form, I have two command button, one will prompt the results in a query table, and the other will prompt a report.
Problem 1
I manage to prompt report with a date range (i.e. two unbound text boxes for start date and end date). But, I dont' manage to disable the filter if the date range is null. Below is the code for this report command button. Should I use a toggle button to make a select case?
Private Sub cmdReport_Click()
Dim varItem As Variant
Dim strDocName As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str2MainCateCondition As String
Dim strDate As String
Dim strSQL As String
Dim strFilter As String
' Build criteria string for 1st ComboBox
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2nd Combo Box
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1toggle button condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " 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.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";"
' Build criteria string for Date
If Not IsNull(Me![dateTo]) Then
strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#"
'Format(Me.dateTo, "mm/dd/yy")
Else
strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#"
End If
' filter string
strFilter = strDate
' Open report
strDocName = "RptCateDateQry"
DoCmd.OpenReport strDocName, acViewDesign, , strFilter
With Reports(strDocName)
.RecordSource = strSQL
.Filter = strFilter
.FilterOn = True
End With
DoCmd.Save acReport, strDocName
DoCmd.OpenReport strDocName, acViewPreview
Exit_cmdReport_Click:
End Sub
Problem 2
I have no idea how to filter the query results by date range in the query table. Below is the code of the query table button, which do not offer the filter feature. Would really appreciate it if you can give me some advice.
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strDate As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str1MainCateCondition As String
Dim str2MainCateCondition As String
Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "QryCateDateForm" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "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"
cat.Views.Append "QryCateDateForm", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then
DoCmd.Close acQuery, "QryCateDateForm"
End If
' Build criteria string for Date
If Not IsNull(Me![dateTo]) Then
strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#"
'Format(Me.textStartDate, "mm/dd/yy")
Else
strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#"
End If
' Build criteria string for 1MainCate
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2MainCate
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1MainCate condition
If Me.optAnd1MainCate.Value = True Then
str1MainCateCondition = " AND "
Else
str1MainCateCondition = " OR "
End If
' Get 2MainCate condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " 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.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & _
str1MainCateCondition & strDate & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("QryCateDateForm").Command
cmd.CommandText = strSQL
Set cat.Views("QryCateDateForm").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
Sorry for posting this question again, as I thought it's better to make it a seperate posting, rather than a reply to my early post. Your advice will be greatly appreicated.
I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.
Here is the query created as a stored procedure:
SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];
What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?
Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?
Thanks.
I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.
Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."
So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:
tblLeadData.AttyID
refAtty.Atty
tblLeadData.PlglID
refPlgl.Plgl
tblLeadData.AssignmentNote
Expr1: [Atty] & " " & [Plgl] & " " & [LeadAssignmentNotes]Criteria: Like "*" & [Who?] & "*"
The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.
When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.
As for my cascading combos, here are the settingsMatterTypeIDRow Source:
SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery
MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter;
On Change Event:Me.cboAtty.Requery
[Code] ....
I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter
data 3 times:Enter Parameter Value: Atty
Enter Parameter Value: Plgl
Enter Parameter Value: Who?
Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.
I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.
problem when I am trying to use Form to filter data from a cross tab query though I have already defined the parameters. The SQL is as under :
//Code start
PARAMETERS [FORM]![START_MONTH] DateTime, [FORM]![END_MONTH] DateTime;
TRANSFORM Round(Sum(CLng([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 (((maindata.field_block)<>"Panna" And (maindata.field_block)<>"Mukta" And (maindata.field_block)<>"CB-ONN-2000/2(NSA)" And (maindata.field_block)<>"CB-ONN-2000/2(BHEEMA)") AND ((PRODUCTION.MONTH) Between Format([FORM]![START_MONTH],"dd-mm-yyyy") And Format([FORM]![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;
//Code End
When I save it prompts for the Start and End Month and When I run the query it pops up the form twice...
I have one form which houses all my product information via the use of a query and subforms where appropriate. I would like to filter it in order to find records missing certain information.
I want to filter the information using a combo box.
The combo box has a user friendly name (Product Dimensions) and the name of a query related to that name (qryfilter_missing_prod_dims - this query is the record source query for the form plus the query that has the missing info, the query is set to return all records that match ie. all records that are missing info).
I want to write a vba code to filter my form based on that query.
And i can't get it to work using the cmd.applyfilter (docmd.applyfilter me.cbo_missing)
I've also tried to use me.recordsource. when i do it filters perfectly, but i can no longer edit any of my data in the form.
How to filter my form using a query that is not in the native form's record source?
how i would filter a form using an option button. E.g i want to be able to click an option button that is called Filter user and it will filter the query based on who is logged in? and a Filter all option that will clear the filter and show all records?
View 3 Replies View RelatedHow can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
View 3 Replies View RelatedI have placed a filter button on a form as a filter and written the following on-click event procedure:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdApplyFilterSort
Me.Filter = "ACCOUNT_DO_NOT_EMAIL = 'HS'"
Me.FilterOn = True
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
I have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
I have a form which contains customer names. I used to be able to perform a filter on the form where when the name was inputted it would autocomplete to the nearest match for me. This has worked great for me for the past 6 months. Just recently though the database will not do this task for me. I have to input the complete name to search for the record.
On a side but related note when doing the filter if I did not have the complete name I was able to search through a listing via a drop down box which listed all the names that have been entered into the database. Now if I use the drop down box it only says:
Is Null
Is Not Null
Has something corrupted my database? I tried using a recent backup which worked for a few hours but then the problem returned. I am hoping that this is something that is easy to fix as my experience with Access is very limited.
Thanks
When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
On a form I have a:
control called "FilterListBox"
list box called "lstCustomer"
option Box called "optCustomerType"
When the user selects an option in the option box, "FilterListBox" is updated to either "1", "2" or "1 or 2"..One of the fields in the query for "lstCustomer" is "CustomerType" and its criteria is set as follows:
[Forms]![frmPrintHowCustomersPaidInvoice]![FilterListBox]
if "FilterListBox" = 1 the query for "lstCustomer" returns the correct records
if "FilterListBox" = 2 the query for "lstCustomer" returns the correct records
But if "FilterListBox" = 1 or 2, no records are returned.
I have a form (Suppliers) with a button that opens another form (Product List).
At the moment the form (Product List) is set as a pop-up form and when opened lists all products.
I want to filter the pop-up form to only display products for the current supplier displayed in the Supplier Form.
I have tried the following below without success - I have varied this but the filter does not apply ...
Private Sub Form_Load()
DoCmd.OpenForm "Product List", acNormal, , "SupplierID = " & SupplierID
End Sub
I have a button that opens a second form and filters that form based on a text box on the first form(which remains open). the problem is that it does not filter the second form unless the second form is already loaded for some reason.
For example if i click on the button it opens the form Unfiltered then i go back to the previous form without closing the first form, click the button again and it is then filtered .
I attached the screen shot of the macro for the button and there are no other events on the second form.
i have one form filtered based on a field value, now i want to open another form which has only the same member ID but not the actual field on what the first form had been filtered (so i cannot just use the same filter) , so what i want to do is that the 2nd form should only show the members which are in the first form.
View 3 Replies View Related