Dynamic Query Help
Feb 7, 2007
I 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
View Replies
ADVERTISEMENT
Jun 30, 2006
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 Related
Dec 16, 2005
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 Related
Jun 14, 2006
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
View 1 Replies
View Related
Jun 14, 2006
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
View 1 Replies
View Related
Jun 14, 2006
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
View 1 Replies
View Related
Jun 14, 2006
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
View 4 Replies
View Related
Jul 19, 2005
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
View 2 Replies
View Related
Dec 7, 2005
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!
View 1 Replies
View Related
Aug 24, 2006
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?
View 3 Replies
View Related
Sep 13, 2007
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.
View 3 Replies
View Related
Dec 15, 2004
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.
View 2 Replies
View Related
Feb 11, 2007
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
View 2 Replies
View Related
Feb 20, 2005
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.
View 1 Replies
View Related
Apr 29, 2006
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,
View 1 Replies
View Related
Aug 29, 2005
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
View 4 Replies
View Related
Jan 21, 2008
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 Related
Mar 31, 2008
Can 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
View 3 Replies
View Related
Feb 23, 2005
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.
View 1 Replies
View Related
Mar 2, 2005
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
View 1 Replies
View Related
Oct 18, 2013
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 Related
Jan 22, 2014
We 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?
View 4 Replies
View Related
Jun 25, 2014
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.
View 3 Replies
View Related
Apr 23, 2015
I have a dynamic cross tab query - thus the column headings will change each time it is run.
At present the column headings are displayed in alphabetical order - how can I change this so they are based on a different order - eg by the descriptions corresponding ID
View 3 Replies
View Related
Dec 17, 2014
I have an access 2010 web database, modified from a microsoft template (Desktop inventory database) that several users will need to log in to. The database contains a table of products.
The challenge is, every user needs to only see a subset of these products and never see the whole list.
At the moment i have some code to modify an existing query based on the logged in user's details. As they log in, some tempvars are created and these are used to modify the query criteria.
This works well when the first user logs in, but the moment the next user logs in, the query is modified again and the product list refreshes and now his products are shown and not the first users! Im thinking i need to dynamically create a permanent query for each user on log in?
Here is my code so far:
Button on login form has the following code that collects the user's details
Code:
Private Sub cmdLoginMine_Click()
Dim ID as long, strEmpName as string,strZondsc as string,strgrpdsc as string
ID = DLookup("ID", "Employees", "Login='" & Me.txtUser.Value & "'")
strEmpName = DLookup("FullName", "Employees", "Login='" & Me.txtUser.Value & "'")
strgrpdsc = DLookup("MyGrpdscs", "Employees", "Login='" & Me.txtUser.Value & "'")
strzondsc = DLookup("MyZondscs", "Employees", "Login='" & Me.txtUser.Value & "'")
TempVars.Add "tmpEmployeeID", ID
TempVars.Add "tmpEmployeeName", txtUser.Value
I then call a function that modifies the existing query, populating it with this users details for the criteria
Code:
qryEdit strgrpdsc, strzondsc, ID
Sub qryEdit(strgrpdsc As String, strzondsc As String, ID As Long)
Dim qdf As DAO.QueryDef
Dim qdfOLD As String
Set qdf = CurrentDb.QueryDefs("InventoryQryforDS")
[Code] ....
The results of the query are shown on a form, which is what is currently requerying and showing the wrong data. The data is shown on a form, linked to one of the new style navigation buttons as shown.There cord source property of the form is the query that's populated as described above.
View 1 Replies
View Related
Jan 22, 2008
Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon
View 4 Replies
View Related