So I have created a select query and I am using it as an all purpose search query. I plan on changing the value of SQL in the query based upon what the user selects as the method of searching. So there is a drop down menu, that allows the user to select the method, this will be expanded in the future but for now till I get this working I am not going to bother with that. That being said, I figure once I get a couple of these the work the others should follow suit. Anyway, here is what I have so far.
Code:
Dim StrSQL As String
Dim qdf As DAO.QueryDef
Select Case Me.Combo3
I have a query, and in order for it to run properly, I've set it to look in an unbound combobox on a form to display the necessary data I want, as long as I never close down the form, this is exactly how I want it.However, if I close this form down, and reopen it, I'm asked by messagebox to get the lookup from a different form's combobox, despite saving the correct form's design over and over again, and saving the actual query, more than once, while in design view.when Access changes the criteria, there isn't even a combobox value on the form it changes to.
I was trying to see if there is a way to convert text to a hyperlink in a query.
Background : I'm pulling information from a lotus notes database using an OBDC connection and storing it into an access database and using that to upload to a sharepoint site. While we are converting over to a sharepoint site, I was trying to make it easy to get back to the original site for each document to check the work.
i have an update query that looks for a product number, updates the cost, and re-calculates the price in two columns based on the salesman's margins. the problem i am having is that it seems to be hit and miss on the first run. if you run it again, it runs the calculations on all the fields. (it has to check / recalculate 16,000 rows.) should this query have vba to make it loop thru the table.
here is the basic layout of the query:
table 1 is newproductq table 2 is Products Field: PriceIn Table: newproductq Update To: [Products].[Cost] UNITS newproductq [PriceIn]/((100-[Margin])/100) PIECES newproductq [PriceIn]/((100-[BrokenMargin]/100)
I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date.
I have a date field NxtAPayDate that I need to update to the same day in the next calendar quarter. I have an update query set up using DATEADD but it does not change the dates. The table name is AutoPay
I am using the Query Design Grid. This is the SQL code behind the query:
UPDATE AutoPay SET AutoPay.NxtAPayDate = DateAdd("q",1,[AutoPay]![NxtAPayDate]);
When I view the results the dates are the same - original dates, when I try to sort the results, I get a message box with the following: syntax error, incomplete query clause?
I cannot get the dates to change by using a specific date either
UPDATE AutoPay SET AutoPay.NxtAPayDate = #1/2/2014#;
I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.
Public Sub SetDate1() 'Apply date filter and rebuild query in real time On Error GoTo Err_SetDate1 If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then MsgBox ("Please Enter Date First"), vbExclamation GoTo Exit_SetDate1
I have a query that is based on Property & tenants. It runs a report that gives me all the property listed with the tenants & other info for the property's. Sometimes the tenant moves out & the property is Vacant, so i have a Vacant flag in the property table.
But when i run the query it prints out the last tenant. Can i pick up the vacant flag & change the tenant name to Vacant on the report? As i don't want to delete the tenant from the tenant table.
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?By the way I am using Access 2000.
Hello Friends I have 100 tables and now I changed the name of tables due to some reasons. Now I must to replace the names in other database objects.Is there any option that can reduce the work load because otherwise it is a tiring job to replace it manually.
Whenever I have a form that is based on a query, I am not able to edit the data in the form. Is there any way to change that? I have all allowances set to yes and Data Entry also set to yes. The form contains data for a single record.
I need help with an update query that will change records for me based on the date
In the record I have a TEXT record called [GI1] that can have a multitude of data in it. The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”
What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }
I have a database that I used as a back-end for an Excel spreadsheet. Now that the database has been moved from one place on the network to another, I need to update the information in my spreadsheet to reflect the database's new name and location. Is there any way of doing this short of deleting and recreating the queries in Excel?
Bit of a strange question / problem. I am using a Dlookup between two queries. Everything runs fine is I leave the join as option 1: only include rows where the joined fields in both tables are equal. But if I change it to number 3 Include ALL records in right table in left it returns a syntax error on the query.
I can run the same query with same relationships of another table and it is fine!
I have a crosstab query that reports KPI numbers for the last 5 weeks, using the week no as the column header. Then I am generating a report from this query. It works 100%
However, when I go to the next week, the report fails as the first week from the previous query no longer exists
So, this is happening...
The first report generated columns called 39,40,41,42,43 because the crosstab query uses week number as the header.
The second report (1 week later) cant find column 39 so it crashes!
Can't see how to change the column header to a generic as it is created by the crosstab.....
Okay, this one should be simple and can be solved in one of two ways. I have two seperate tables with a 12 digit identifying number that is ALMOST exactly the same in each. I am trying to match up these two tables, which works amazingly well, except for the occasional case where the 12 digit ID (which is called API) ends in 01, instead of 00. The last two digits do not make a difference at all, but in one table it will end in 00 and the other it will end in 01, even though they are the same record.
Basically, it looks like this... Table 1---------------Table 2 541236554700-----541236554700 541236123700-----541236123700 443231246700-----443231246701
So basically, in my final query, the last entry will not show up since these two are not related by this API (ID) number. Any way to either replace the 01 on the end with 00... OR to remove the last two digits?
Say you have multiple excel sheets where forecasting is done daily.
For example, sheet one headings: Depot, department, location, 01/09/10, 02/09,10, 03/09/10
and then second sheet is similar but: Depot, department, location, 02/09/10, 03/09/10, 04,09, 10 - as you can see each day the forecast starts a day after and ends one date late (14 days each in the real one)...
How to I join all these forecasts into one table; vertically with each forecast identified by the day it start for analysis later.. rather than doing cross-tabulate which would take ages, I just need to keep adding more excel sheets but the headings change as they are dates...
in my assignment, I am told to change my prior query by continuing to display the sum, average, and pledge numbers. what is required, is for me to display these totals by the names of the pledges, not just a grand total. how can I sort the query by name?
OK…procedurally I know I am probably doing this wrong, but that is the way the database is set up…so let me try to explain.
Query A has the same fields as Query B. Query B has two fields which are parameter values (i.e. when you run the query, Access prompts you to enter two values). Query A gets these values from a table.
The process I have for getting a report depends upon whether I wish to hard code the values of the report in, or whether I wish to use the information from the table that Query A uses.
Depending upon my choice I end up renaming Query A or Query B to Query C.
All of my subsequent queries utilize the information in Query C to make the necessary calculations and data massaging in order to spit out a Report.
What I would like to do is have a toggle control/radio button (on a form), which will rename Query A to Query C or Query B to Query C, depending upon which choice I click on the toggle button.
My issue is I do not know how to change the name of a Query in VBA. I tried to look up how to do it using [DoCmd.RunCommand acCmdRename], but I couldn’t find any examples which would help and don’t even know if this is how I should go about renaming these queries.
I hope I’ve explained myself correctly… Does anyone have a clue as to how I could do this?
I have a query that I want to run for multiple tables of the same structure. Instead of creating a query for each table, is there any way that I can write some code or SQL statement that will change the table that the query is using? Thanks!
Not sure if this is in the right forum. :confused:
But here goes, my question is;
Is it possible to update the criteria by using a form?
What we are looking to accomplish, is a report that prints out labels in relation to the customers that are chosen in the query. What we want the user to be able to enter multiple Company ID's on this form, which are then picked up by the query and the companies name and address printed out via the report.
The printing out of the report from the form we have done.
I'm trying to create navigation buttons that navigate in alphabetical order. I know that the easiest way to do this is by creating a query and using the query as the recordsource for my navigation buttons, but for the life of me I cannot figure out how to actually change to the recordsource to that query for only that navigation button.
I am working on a database which has many queries that count different statistics of clients (race,gender,ect) during specific date ranges. I would like to set up a form that you could go in and enter a date range into a text box and it would update the criteria (date range) for each of the queries. How would this be possible? I have looked at some past threads similar to this but they didn't help.
