Between [Enter Beginning Date] And [Enter End Date]
Run and enter 07/01/2014 and 06/30/2015
The return one record dated 07/01/2014
Run again enter 07/01/2014 and 12/31/2014
Return all records between those dates.
when I put in dates for 2014 to 2015 it does not return all records between those dates only the records for the beginning date, is there something I am missing asking for records from one year to another?
Is it possible to put a combo box with a drop down list in the Enter Parameter Values of an update query so that the user selects from the list instead of typing the required value?
Normally I would run a query like using 'CurrentDb.Execute' so that no user prompt occurs, but how do I avoid prompts when I need to run an update query?.
I'm using DoCmd.OpenQuery "qry_UpdatePW" with no joy!
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
I have a database called Tables.mdb with a Make Table Query called Qmak_Tables that I want to run nightly.
I have set up a Scheduled Task to run Tables.mdb every night. I have created a Form that loads on start-up. I have created a Macro that runs the Make Table query when the form loads. I have also de-selected the confirm record changes/document deletions/action selections in Tools > Options > Edit/Find menu so the query won’t be stopped by a prompt that needs manual input. Here’s the catch:
After the query runs, it displays the prompt, “The existing table will be deleted. Do you want to continue?” How do I disable this prompt so my task can continue to run?
I have this query that finds people based on a given id number that they own. The ID number is in the format of (0000-0000) The tables have input masks so that when entering the ID number all the user has to worry about is entering the numbers. I have a report for that shows every customer in the database and i want to be able to copy and paste the (0000-0000) portion of the ID number but the query is only able to find customers by (00000000) format is there a way that i can get the query prompt to ignore the dash in between the numbers or would it be easier to remove the input mask and enter everything manually?
it also prompts twice when i try printing or try to save the current page as a pdf and also when i switch record? i think its a problem with the subform as it always is trying to fetch data. would there be a way to make it so that the subform and the form are linked so that the parameter query prompts once and the information stays there rather then having to prompt again when printing or saving as pdf?Two more things - on my total value box i wish to be able to always show 2 decimal places
The code already in place is :
Net : =Sum([TotalValue]) VAT: =Sum([TotalValue])*0.2 Gross: =[Text8]+[Text10]
(Net is Text8 and VAT is Text10)
i have already specified that the decimal place value is 2 instead of auto but it still doesnt work - if the number is a whole number i still wish to see .00 at the end of the field.One last thing - Would their be a way to carry over the information from my delivery note to my invoice note? which would save a lot of time retyping the parameter query.
I have a couple of questions which are sort of related. They both revolve around Parameters with a Query/Report.
Here is a bit of background. The source Table is formatted in a way similar to this:
Code--Account#--Expense Description--Jan--Feb...
When running the Query, and associated Report, the user is prompted to input an Account# via a Parameter that I set up. The dialog box pops up with the word "Account#" and a box for the user to enter by what account that they want to restrict the Report. [Question 1] - Is there a way to make this dialog box contain a drop-down box with a list of predetermined accounts instead of the aforementioned entering of an account number? If not via this dialog box, is there another way?
Also, multiple accounts roll up into a single P&L line item. For example, let's say that accounts 1234 and 5678 both make up "Equipment". [Question 2] - Is there a way to run the Report in such a way to return all accounts which roll into a line item? I want to have the ability to run by either an individual account or the P&L line in total.
I this is a simplifed version a saved query called "qryTest"
PARAMETERS [gUserID] Long; SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');
UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');
how to i call the result from code or from the query manager and include the parameter so there is no prompt that comes up?
like Me.ListNew.RowSource = "qryTest " & gUserID &";" or Select * from qryTest , 31 - where 31 is the value fed to the parameter
??? does anyone know? I know it can be called in APS like this:
Dim Conn Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open strConn ' strconn is my connection string set rsP=createobject("adodb.recordset") conn.qryTest 31 .....
I can't believe I can't do it from the query string! Heeeellpppp!
I have a table of data going back to 2007 that needs to be looked at on a monthly/quarterly/annual basis. I am able to filter the data when running a normal query by using
Between [Start Date] and [End Date]
in the Criteria section of the Date field. I now need to apply this same idea to a crosstab query. My current set up is:
[Gender]- Group By / Row Heading [Plan Type]- Group By / Column Heading [Pmt Amt]- Sum / Value [Date]- Where / Criteria = Between [Start Date] and [End Date]
I am getting an error message that says:"The Microsoft Office Access database engine does not recognize '[Start Date]' as a valid field name or expression."Am I setting something up incorrectly or is it impossible to use input prompts in a crosstab query like this?
I had an issue with writing LIKE statements in query criteria yesterday [URL]....
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Code: SELECT tblPeople.name, tblPeople.num FROM (tblPeople INNER JOIN tblAddresses ON tblPeople.name = tblAddresses.name) INNER JOIN tblPets ON tblPeople.name = tblPets.name
[code]....
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
i have a subform on a form with Source Object="Query.myquery", my form is generating a sql string dynamically and assigning this sql string to the RecordSource property which works fine. But now if the user changes the width of the columns in the subform and close the form , a prompt is thrown asking if the "myquery" needs to be saved. I dont want to save the settings of "myquery" but at the same time avoid this modal dialog. If i use DoCmd.SetWarnings=False , i dont see the warning anymore BUT the query gets saved.
I am using "myquery" to just display column names in the subform when the forms loads and also without it i cant directly use RecordSource property in my code.
In trying to make a certain job more simple, I need to be able to allow a user to click on a shortcut to a query and then enter several parameters. Two of them will be Start Date and End Date. I understand how to do that portion; however, the users must also be able to type in multiple page numbers and display the records where those page numbers appear (one field). For instance, ordinarily, I would just go in and type "46" or "48" or "50" and it would display all of those records where the field contained one of those 3 numbers. I would like the Parameter Query to prompt the user to "Enter Page Numbers" and maybe even give a format; however, when I try this, it doesn't work no matter what syntax I use to enter the page numbers.
After I do a "DoCmd.RunSQL "Update ..."" I am asked if I really want to perform the 'Update'. Does anyone know if there is a way to get rid of the prompt and just carry out the query?
I have noticed that when i am editing an object within my database, specifically a query and click to close it, it doesn't prompt me to save the changes and does it automatically. There have been cases where i do not want to save the changes and just wanted to know if there was a simple setting to turn off automatic saves?
Have a master table with say Product no Product description qty
Then I have another archive table with Product no 1_qty Q2_qty Q3_qty O4_qty
All I want to do is to (at the end of each quarter) archive (transfer) the qty from the master table to the archive table at a user level. Meaning I need a control button which does the lot and I suppose prompts and asks which quarter do I want to archive to. I know how to manually do all that, but is it possible to have a prompt which determines what field is used i.e Enter 1 then the field Q1_qty is udated. Hope I have explained this ok. Appreciate some help. Thanks Norm
I have a query that ask for an input. I was wondering if there is any way to make the prompt have a combo list, so the users can scroll and select the input ,which will generate the Query results,instead of having to type the input in. will really appreciate your help.
I have created a database to store information from a mine site. I have set up a switch board and most users will just view reports or graphs that have already been designed.
I want the general users to open the file read only without having to open access and use the open read only command. I also don't want to go down the path of database security if it can be helped.
I read in the help page that there is a read only prompt that can be flashed up when the file is opened. Unfortunatly the help page suggests, Tools > Options > Security Tab (tick read only prompt). In office 2003 this option no longer exists (if it ever did)
Any ideas, would be good if i could add something to the shortcut and then have some uses use this shortcut.
I really need some help guys. For some reason Access always asks for a login/password even if the database isn't protected. Even when I start a brand new database the prompt comes up. Any ideas as to how to get rid of this.:confused:
I am hoping to create form that will prompt the user to enter default values for certain fields ie Week No and WB Date before allowing any data to be input into the form.
I was editing one of my tables in datasheet view and when I closed the table, Access asked me if I wanted to save changes. But I didn't make any changes to the design of the table or the filters I had set up. I just edited the data within datasheet view. The reason this is bothering me is that I am afraid I accidentally changed something. The only explanation I have for why it asked me if I wanted to save changes, is that I used the "undo" command to cancel a typo that I made while editing in datasheet view. When I hit "undo", it fixed the typo, and scaled back up to the first record of the datasheet. At first I thought using the "undo" command might have been the culprit, but when I re-opened the table and used the "undo" command again, it undid the change, but it didn't scale back up to the first record of the datasheet, like it did the first time I used the command. Anyway, I am staying away from the undo command from now on. It's about worthless in Access anyway, because it only undoes your last action. Have any other Access users been asked to save changes when they know they haven't made any changes to the design of the table?
Once at the beginning of each month I extract all new cases from last month into a table. The table has a generic name as I repeat this step monthly. I would like to save or archive the data each month without having to manually copy and paste the table and rename it, prior to the new extraction, so that I have a record of each month. The archived table will not be used in any queries or reports.
The table I want to copy and rename is named 'tblSampleFrame'
At the beginning of the month before I delete the contents of the 'tblSampleFrame' to pull the new data, I would like to copy the old table and include a date in the new name. How would I set up a button on a form to copy the file and prompt me for the new file name? The new name would be 'tblSampleFrame_mmyyyy' where I can specify which month and year.
