After reading some of what people already had in this forum, I was still left a little confused. Say I have a form with three combo boxes and I want the user to be able to select something from all 3 or just one at a time. Which kind of query should I be using. Right now I have a select query, but it doesn't work right due to the Where Clause, which will work 1 way or the other, but not both(that I know of). Any advice would be appreciated. Thanks.
Bobby
i need some major direction!
here's what I need to do....
i need to generate a report based on a table(no big deal).
However, I need to generate a query that lists all users from the table(still no big deal).
Here's where the fun comes in...
I need to use that query to populate a listbox and allow multiselect on the listbox. The table data that is related to the values from the listbox that are selected are NOT to be showed on the report. So I imagine I need to create a query where the criteria is <>the value(s) in the listbox. So I basically have two problems...
1. getting the value(s) from the listbox (the only way i seem to be able to get a value from the listbox is if I do not allow multiple selections--i'm obviously overlooking something)
2. using the value(s) as criteria to limit my query results(not sure how i would do this, since it's more than one value--possibly an array??)
I guess i could also do it the opposite way, too(select users that you want)...but either way, i have no idea how to go about it. Please help~I'm going crazy!!
thanks
*j
I want to know if it's possible to make a dynamic select case. In my form I have 3 separate combo boxes. What I want to happen is depending on what was selected in one the options in the other two change and if you select something in the second the option in the third narrows down again. The user can select these in any order. They can use one by itself or all three independently. In my code I can do this with many select statements, e.g
Select Case Me.combobox1.Value
Case "choice1"
Forms!CurrentForm!combobox2.RowSourceType = "Table/Query"
Forms!CurrentForm!combobox2.RowSource = "SELECT fieldname FROM tablename WHERE fieldname = '" & Me.combobox1.Value & "'"
End Select
In the place I have "choice" is it possible to write something along the lines of,Me.combobox1.value = "SQL Code"..The idea that this code would still work if the user adds more data to the tables which these combobox choices come from.
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1
FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION
WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate()
'Dim LocQryStr As String
'Dim r As Integer
'Dim ctl9 As Control
'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
View 6 Replies View RelatedI have a form where a user chooses different criteria from listboxes and then I run out and format the SQL statement.
This is the query that I am trying to replicate using VBA code. Note: the Location_ID, Ship_Day, and Final_Dest come from the values in the listboxes.
I am getting a syntax error and I cannot find it anywhere. Also, is there an easier way to do this?
SELECT [(Table) Denton Routing].LOCATION_ID
, [(Table) Location].NAME
, [(Table) Location].CITY
, [(Table) Location].STATE
, [(Table) Location].REGION
, [(Table) Denton Routing].UNIQUE_LANE_ID
, [(Table) Denton Routing].CARRIER_ID
, [(Table) Denton Routing].[SHIP DAY]
, [(Table) Denton Routing].[DELIVERY DAY]
, [(Table) Denton Routing].[TIME AT LOCATION]
, [(Table) Denton Routing].STOP_NUM
, [(Table) Denton Routing].NO_OFF_STOPS
FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = [(Table) Denton Routing].LOCATION_ID
WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID
FROM [(Table) Denton Routing]
Where [(Table) Denton Routing].Location_ID = "13176AA"))
AND (([(Table) Denton Routing].[SHIP DAY])="MONDAY"
AND [(Table) Denton Routing].Final_Dest = "DENTON"));
Here's the code:
Private Sub cmdRunQuery_Click()
Dim Db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set Db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
'On Error Resume Next
'Db.QueryDefs.Delete ("Dynamic_Query")
'On Error GoTo 0
where = Null
where = "WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID FROM [(Table) Denton Routing] where"
where = where & " [(Table) Denton Routing].[Location_ID]= '" + Me![Text35] + "'"
where = where & " AND [(Table) Denton Routing].[Final_Dest]= '" + Me![List29] + "'"
where = where & " AND [(Table) Denton Routing].[Ship Day]= '" + Me![Combo46] + "'))"
MsgBox (where)
Set QD = Db.CreateQueryDef("Dynamic_Query", _
"Select SELECT [(Table) Denton Routing].LOCATION_ID, [(Table) Location].NAME, [(Table) Location].CITY, [(Table) Location].STATE, " & _
" [(Table) Location].REGION, [(Table) Denton Routing].UNIQUE_LANE_ID, [(Table) Denton Routing].CARRIER_ID, [(Table) Denton Routing].[SHIP DAY], " & _
" [(Table) Denton Routing].[DELIVERY DAY], [(Table) Denton Routing].[TIME AT LOCATION], [(Table) Denton Routing].STOP_NUM, " & _
" [(Table) Denton Routing].NO_OFF_STOPS FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = " & _
" [(Table) Denton Routing].LOCATION_ID " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub
As much as I would like to tell my boss he's insane I'd also like to keep my job. I know this is possible, but it sounds very complicated. Basically, I need a way to generate queries on the fly. This is geared towards someone who doesn't know SQL of course. I know basically what he's looking for, comparing sales over some period of time. However, he might want annual numbers, quarterly numbers, percentages, overages/shortages, and any kind of sales related query you can think of. The only way I figure I can do that is to have a form build the SQL statement, save the SQL statement as a query, then have him open the query. Is there another way I can do this without necessarily saving a query first?
View 6 Replies View RelatedDear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
Hi,
I have the following query:
SELECT *
FROM qryForCreateReportSold
WHERE (((qryForCreateReportSold.Fund) Like '*STP*') AND ((qryForCreateReportSold.[Sale Date])>=#7/1/2005# And (qryForCreateReportSold.[Sale Date])<=#6/14/2006#));
The user fills out a form and hits run query and the value STP gets passed into the query criteria.
My situation is that there are claaifications such as S/STP, E/STP etc and the problem is all these projects also show up whereas I want projects ONLY
under the STP classification.
I guess the problem is due to the fact of the *STP* which means pick all records that have an STP in them...
If I manually get into the query and remove the * * then the projects ONLY with STP show up.( which is exactly what I need)
Can some one suggest something to remedy the situation.
Thanks
My boss asked me to do something today that stumped me. He wanted me to make a query, where when you're selecting the parameters you could chose whether or not you wanted certain fields to be visible.
On top of that I need to be able to chose whether I want it to be shown on screen in a query, printed as a report, or exported to excel. This is what makes it tricky. I could easily do a bunch of If...then statements to generate the SQL query and only show the fields necessary, however the report and the query view both have the fields on them in design view. If I cut the fields out of the query at runtime, then I get the nasty "Input parameter" popup box.
So I've been bashing my head off the keyboard trying to figure out how I'm going to do this. One other theory I tried to put into use was having them left on the form/report, but if the checkboxes were not selected then it would set them to visible = false at runtime. Sounds good in theory, however Access decided to ignore this block of code and just show the fields regardless of what I told it to do.
So if anyone could provide me with some insight or suggestions, I would certainly appreciate it.
Thanks in advance!
Not sure if this is possible but I figured I'd ask.
I currently have a form/update query that allows me to change a persons last name in a record depending on the value I enter in an unbound text box on the form.
Is it possible to make this query dynamic so I change field names on the fly instead of it programmed for lastname. Example, I want to change firstname instead of lastname.
Would I need another query for firstnames?
Hello Access Expert
Is it possible to call a function as the criteria for a field in a query.
I have used a function that returns a boolean into the criteria field and it worked but when I create a string expression for the criteria field it doesn't seem to work.
For example I have created this simple function to generate a criteria
Public Function AcceptedDays() As Variant
Dim Days As String
Days = ""
If Forms![DrillDown]![Sunday] Then
Days = "1"
End If
If Forms![DrillDown]![Monday] Then
If Days = "" Then
Days = "2"
Else
Days = Days + " OR 2"
End If
End If
If Forms![DrillDown]![Tuesday] Then
If Days = "" Then
Days = "3"
Else
Days = Days + " OR 3"
End If
End If
If Forms![DrillDown]![Wednesday] Then
If Days = "" Then
Days = "4"
Else
Days = Days + " OR 4"
End If
End If
If Forms![DrillDown]![Thursday] Then
If Days = "" Then
Days = "5"
Else
Days = Days + " OR 5"
End If
End If
If Forms![DrillDown]![Friday] Then
If Days = "" Then
Days = "6"
Else
Days = Days + " OR 6"
End If
End If
If Forms![DrillDown]![Saturday] Then
If Days = "" Then
Days = "7"
Else
Days = Days + " OR 7"
End If
End If
AcceptedDays = Days
End Function
If however, I literarly type what the function produces into the query grid field the query generates the correct result. On the other hand if I send the result of the above function the query doesn't work. Any explanations ?
Thanks so much.
Hi all,
I’m trying to create a report with dynamics columns generated by a crosstab query. I have set 4 unbound text boxes for header and detail sections on the report. The column heading from the crosstab query could have 1(Column), 2 or 3 different values, which will be my heading in the report. I want to be able to show 1(Column) if it has a valued and hide the rest 2, and 3 heading. Now column 4 headers will be my Total heading as well for values, which will be coming from the sum of 1,2 and 3 if they happened to have values.
Can anyone help on this please, and let me know if I should bring some more information to you guys.
Thanks so much.
Hi, I'm building a form where the user can choose various options to build up the query they want. It's going to be easier this way because the different combinations they might want would mean me writing well over 100 different queries. So I think I can build the sql statement in vb and then get a recordset back with the results, but the question is how to display it. I can't just assign the recordset to an existing form because the fields to be displayed will be different for different options. Ideally I'd like it to open just as if I had run:
DoCmd.OpenQuery <name of the query>
But it doesn't look like this is going to work for a query I've generated in the code, even if I use a querydef object (correct me if I'm wrong?). So does anyone know how I would go about doing this? (It would also be fine if the results appeared in a subform on the form I'm calling the query from, but I can't see how to do this either).
Any help gratefully appreciated.
Sim Bamford
I have the following query, which I know works.
The query is called "products_due_in_query"
SELECT Sum([Supplier_order_line].[quantity]) AS [Due_in]
FROM Supplier_order_line
WHERE (((Supplier_order_line.order_line_status)="Awaiting delivery") AND ((Supplier_order_line.product_number)=[product_number_combo]));
product_number_combo is a combobox on a form called "product_enquiry".
I have a text box on "product_enquiry", which I want to show the value of "Due In" (from the query), when the user selects a product_number from the product_number_combo combobox.
How can I do this? I have tried setting the text box control source to products_due_in_query.Due_in but all I get is "#name".
I thought about doing it with VB code, but dont know how.
I have used .recordSource before, to assign a value to a combobox, but I dont know how to do it for a text box.
Can anyone help?
Thanks very much.
Hi, hope this is a reasonable thing for semi-beginner to intermediate person to do.
Our MS Access 2000 db shared on a server stores projects, categories of projects, clients, contractors doing the projects. Contractors are offered projects within their chosen categories based on their order on the list (last company who was offered a contract goes to bottom of list, like that).
Currently we print out a phone list of the contractors (in desc. date order of the most recent offer accepted or refused), then we phone down the list until someone accepts. Later we enter all those offers in order on frmOffers to store the date & time stamp of that offer.
Problem is, several contracts can be on offer on a given day, so when a company accepts/declines Project A, that company is still showing as high on the (paper) list for Project B. So when we have 3 people phoning they don't know they've offered multiple contracts to the same company this morning.
Is it possible for a form "Offers" to be based on a query that keeps updating like that? For example, you open frmProject to display details of a certain Project, cmd button to open frmOffers (continuous form, showing all offers so far with company, phone #, "accept"/"decline" & date/time stamp). Then, when you tab to the next record, can you have it show which company is next in line (even as the person beside you has updated their frmOffers in the meantime)?
Would you need to have a separate table storing only the most recent offer for each company? If so, how would you get that updated all the time?
Anything you could offer to point me in the right direction would be very much appreciated!!
Thanks,
Hi,
I wish to use the results of a crosstab query in another query which will make table. The trouble is the field names are not always the same in the crosstab.
The results are coming from a normalised table so if there are no records for a particular field then that field will no appear in the crosstab.
I need to make my 'make table query' (qryTabletImport_CollarMakeStaging) account for the missing fields names when it tries to make the new table. Since I have made the make table query with all availble field names I get this error when some are not present.
<the jet engine doesnot recognise 'qryTabletImport_CollarConvert.Hours' as a field name or expression>
(because the field Hours doesn't have any data in the normalised table)
make table query - qryTabletImport_CollarMakeStaging
Crosstab query - qryTabletImport_CollarConvert
normalised table - tblTablet_HOLEDETAILS
Hope this makes some sense to someone.
I am ok with vb so am willing to go that way but getting my head around which way start is another issue
Cheers
Hello to everyone!!!i need to ask somethingI CREATE I DYNAMIC QUERY .....HOW CAN I CHANGE THE NAME OF THE COLUMN HEADINGS?THANX IN ADVANCE
View 1 Replies View RelatedCan anyone help?
want to establish the criteria for a report to include 6 months (into the future) from todays date. Have tried dateadd function but this doesn't seem to allow dynamic (automatic) start of period date
Cheers
I have Form A that has a combo box and a "Submit" button. When the user clicks on the Submit button it needs to open Form B. Recordsource of this Form B is "Query B"
Item selected on the combo box becomes the criteria for "Query B and Form B needs to be opened based on that criteria.
What is the easiest and fastest way (no dlookups please!) to do this?
If an illustration is possible through an example that will be wonderful.
Thanks in Advance.
Hello All,
How can I create reports in Access based on dynamic queries? I did a lot of search on this but couldn't find anything reasonable.
Any help to get me started will be extremely appreciated.
Thanks
Sometimes i have a problem with dynamical queries. For some of these i need to put in the same parameter value two times, before it works. Why this occurs?
View 2 Replies View RelatedWe have a ComboBox on a form with the months: Jan, Feb, Mar, Apr, etc.
We have fields in a budget table named: Jan, Feb, Mar, Apr, etc.
We want to create a query that pulls the correct field based on the value in the combo box.
This is what I have that doesn't work:
MyField: [Query1].[SumOf] & Forms![Main Menu]![test]
How to do this?
I am trying to build a function that will create a dynamic query for a chart on a Subreport.I am not exactly sure I am going about this the right way, but I need the user to be able to change selected fields for use in the query. I have a form with 3 combobox controls for selecting options to change the SQL statement. So far my code only deals with one of these comboboxes for simplicity. There is a button to call my function. Currently, the function is setting hidden text box values based on the combo controls, but I'm not sure if this is redundant.
I am using this as my guide for building the sql, but I am having trouble picking up the values in my text boxes for use in the SQL. [URL] .....
Code:
Option Compare Database
Option Explicit
[code]...
how do I get a value from an unbound textbox on an unbound form into a string to use as sql? The value in the textbox is a number.