I am trying to copy the parameter value that is entered to a variable so that it can be used to run other queries without typing it in over and over. Once my form is finished it will run 4 queries, and the current setup is that I would have to retype the parameter each time.
View RepliesI have an append query that currently looks like this URL...This query automatically adds the machine parameters for a product code and lot number into the running condition log. This is so the user does not have to manually go in and tediously select each machine parameter.
The running condition log also has a date field to specify what day and record number the machine parameter's value was recorded on. When I run the query the appended rows look like this. The product and lot are defined by user parameter and there are actually about 36 machine parameters
Code:
Productstockcode LotNo Day Record Parameter ActualCond
PE-500 130816m71 StockTemp
PE-500 130816m71 Zone 1
PE-500 130816m71 Zone 2
My question is: how do I modify the query to automatically add the date and record number in one shot? It needs to be user defined at the time of the query because this data is not stored anywhere else in the database. For each 36 machine parameters the day and record would be the same.
How could i get output of a Query in a VBA variable ?? I'm trying as below butIi'm getting error:
Dim output As Integer
vari = DoCmd.OpenQuery("qryCompare", acViewNormal, acReadOnly)
Output of a query is a single MAX value.
ACCESS 2007
I've got a table in which some records are associated with a number (numeric field): 691, 692, 693 etc. My parameter query asks for the number I'm looking for (parameter) and should return all records associated with that number or with that number minus one.
Something like this:
enter parameter
find records
in which number = parameter
OR
in which number = (parameter - 1)
How can I do that?
I have a report that is fed by a parameter query. When a command button (Command23) is clicked a parameter box opens and the user enters a subsystem number. A report for that subsystem then opens.
I want to open the report with VBA and show a message if the user has failed to enter a subsystem number in the parameter box and just clicked OK.
The problem is I don't know how to refer to the parameter box and detect if it has been left empty before clicking OK. I first tried to use IsNull in an If statement with Command23.Value, but now realize it is the value in the parameter box I am after, not the "value" of Command23.
How can I change my code to detect if the parameter box has not been filled out?
Private Sub Command23_Click()
If IsNull(Command23.Value) Then
MsgBox "You did not enter a Subsystem", vbOKOnly, "No Criteria Entered"
Else
DoCmd.OpenReport "rptTESTInfoBySubsystem", acViewReport
End If
End Sub
We're using MS Access 2003 on a MS Access 2000 database file. There are several queries, tables, forms within the database. When we dclick a form the "Enter Parameter Value" dialog box appears requesting input for "Enter Job ID#". The dialog box appears to be popping up due to settings within another query that is systematically called. We oblige and are taken to the results. When we click a dropdown box it is listing all the items in the database but it isnt filtering the contents based on the earlier provided parameter value as we'd hoped.
If I modify enter Design View and modify the dropdown box's RowSource SQL statement to include a WHERE clause all is fine but the provided value is fixed as opposed to a variable.
Is it possible to use the parameter value input from the dialog box as a variable for my dropdown box RowSource? I;ve already tried [MyExternalQuery].[Enter Job ID#] as a variable but no luck -- i just get another dialog box asking for input.
Im new to Access.
Hello all,
I realise this issue is a common one and it is usually down to simple typos (I've looked at several similar posts) but none of the advice I've seen has solved my problem. I've designed the following Union Query:
SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 1]
UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 2]
UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 3];
It should be very simple and I've checked it over and over, but when I run the query an Enter Parameter dialog appears prompting me to enter Email.
If I just click OK I get all the records but with the email field blank.
Similarly if I type x@y.com it returns all records but with the email fields all containing x@y.com
Advice would be very much appreciated!! Thank you in advance.
Abi
(a) The On Open event of my report contains a VBA Sub that assigns a value to a variable named vShow. (Tracking the sub in VBA shows that vShow is correctly being assigned the desired value.)
(b) I then use vShow to try to control a calculation that occurs in one of the text boxes of the detail section in the report
(c) Basically, the control source of the textbox contains (in part) the statement (vShow>[fieldA]), which is embedded in a longer function.
(d) However, when I type this in at Control Source box of the Data tab of the the Property Sheet, Access always substitutes "[vShow]" for "vShow".
(e) So what I get is ([vShow]>[fieldA]), which treats vShow as the parameter of a parameter query.
ANY WAY TO OVERCOME THIS AND HAVE vShow recognized as the variable I defined at On Open? Perhaps there needs to be a variable declaration there, that I don't understand.
The basic form (vShow>[fieldA]) does not seem to be the problem, because I can enter, e.g. (500>[fieldA]) and everything works OK.
I'm sorry to have to ask such a basic question, but I'm having trouble figuring out the syntax for saving a field value, in the current record, to a variable. I appreciate the help.
View 2 Replies View RelatedI have a parameter form I used to enter data for a query. I want it to clear any previous data entered when it opens. I have tried the below without success. I also have the fields defaults set as "" and the form set at yes on data entry.
Private Sub Form_Open(Cancel As Integer)
Me.BldgCmb.Value = ""
Me.StateCmb.Value = ""
Me.CityCmb.Value = ""
Me.AddCmb.Value = ""
End Sub
I am still trying to copy an Excel file from a parent folder to a sub-folder, that this code creates, and name the copied file with the name used to name the sub-folder, using the name entered in a control field (named 'TheDirName) of an Access 2007 form. Here is my VBA, which names the copied file "TheDirName.xlsx.:
Private Sub MakeDir_Click()
Dim appExcel As Excel.Application
Dim lngLastDataRow As Long
Dim Folder_Path As String
'Create path for customer folder & files
[code]....
Is it possible to limit the type of data that can be entered in a query that has a LIKE criteria?
( Like [Enter Data] & "*")
limit to two digits or any number of digits, or limit to numbers only or letters only. .
Using the following filter works fine
Student.Filter = "[Class ID]= 270"
But when replaced by the following it does not work:
p = 270
Student.Filter = "[Class ID]= p"
The error message is: 3061 "Too few parameters. Expected 2.
These codes look identical I wonder why the second one doesn't work.
I have a query that has three fields: 1) Days 2) Bit Size 3) Feet. The Days field has a date range specified in the Criteria that comes from two unbound fields on a form. I have a second query based on the first: Two fields are Days with the Total as Min and Max. Three fields are Feet with the Total as Min, Mac, Avg. One field is Bit Size with the Criteria as a specified bit size (i.e. 6.125). These queries are used on a report, and all data is inputted from forms prior to the report.
The queries work as expected with a specified bit size. My problem is that while Bit Size is normally chosen from a list of sizes, a custom size can be inputted. I am unsure how I can specify the Criteria in Bit Size to be a custom size..? To compound things further, more than one custom size can be used.
A) I need a way to pick out the custom sizes from the first query and B) use them as the Criteria for the second query. If I use VBA, I assume I could complete B) by using a WHERE clause (if the custom size(s) has been defined)...
I have a folder which holds 1000s of pictures organised in subfolders, for example:
D:~AI Database Print Scans2009family and
D:~AI Database Print Scans2009holiday
And so forth. In total at the moment 17 main subfolders, each of which hold another 2-3 subfolders.
I am putting together a database to bring pictures together with all sorts of details. I import the picture via hyperlink and complete the various fields. All that works fine but there are a lot of pictures! And it gets confusing to see which ones have already been entered into the database and which ones haven’t.
One solution for this is to copy the pictures that have been “completed” to another folder. I have found a way to do that:
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = Forms!frmPrintDetails.txtPath1
newPath = "D:~AI Database Print ScansCompleted_Entries"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath, newPath
Set fs = Nothing
Works fine, but the problem is that I loose the subfolder structure, it copies all pictures to the same main folder. I’d like to maintain the subfolder structure and add code to create the correct subfolders and next copy the picture. With the different paths I’m lost. I don’t know how to extract the correct path in code. I’d also like to either rename or remove the picture once I have completed entry of the details. The problem I have again is that I don’t know how to code for a changing path.
Im trying to pass a list box variable in a select query. I understand you cannot pass a variable directly but have to pass it through a function. I may be wrong in this, but whatever I do I cannot get it to work. Here's my code:-
Public Sub GetEquipment()
List387.RowSourceType = "Table/Query"
List387.RowSource = "SELECT findequipstr() FROM Equipment"
End Sub
Public Function findequipstr() As String
If IsNull(List371.Value) Then GoTo function_end
findequipstr = List371.Value
function_end:
End Function
If I MsgBox(findequipstr()) within my Getequipment function, the variable is messaged,
I have a query like this
SELECT qryBONUS_pre.PACIENT_ID, Sum(qryBONUS_pred.TOTAL) AS SumOfTOTAL
FROM qryBONUS_pre.TOTAL
GROUP BY qryBONUS_pre.PACIENT_ID;
I like SumOfTOTAL to put into a variable
How to do it
I wan't to create special query who show only special package. It must work that.
1. Client logg in. System rember his chose. varPrawa="Client" and varIDLogin=5
2. Client click at button "Show Your Package". At that moment query haven't got any criteria so it show every package but it must show only package with IDClient=varIDLogin so only package with IDClient=5 it is easy to create. I tell more it is still easy to join other person but only with varPrawa="Client" i want to create one query who must select by.
WHEN varPrawa="Client" then show only package with IDClient=varIDLogin
WHEN varPrawa="Kurier" then show only package with IDKurier=varIDLogin
WHEN varPrawa="Spedytor" then show only package with IDSpedytor=varIDLogin
WHEN varPrawa="Administrator" then show all package without varIDLogin criteria.
I create subraport who use chosen package in my list and display all special data using varaible but my problem is how to create query whose can select and display only package of that Client or Kurier or Spedytor.
At the end
When Client is online he can see only his packages special IDClient (any IDKurier, IDSpedytor)
When Kurier is online he can see only his packages special IDKurier (any IDClient, IDSpedytor)
When Spedytor is online he can see only his packages special IDSpedytor (any IDClient, IDKurier)
When Administrator is on line he can see all packages (any IDClient, IDKurier, IDSpedytor)
How i can create this query?
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
is it possible to name a field in a query or a table with a variable name?
For example:
I want to choose in listbox "fieldname" the name of the field.
And the query field name looks like:
Code:
forms![testform]![fieldname]: FieldOfTheTable
Unfortunately it doesn't work.
Also if I save the choosen fieldname in a vba function.
Code:
fieldNameFunction(): FieldOfTheTable
This doesn't work, too.
I have a table called [Attachments Table], which has the fields:
AttachNbr as autonumber
Task ID as number
FileN as hyperlink
URLText as long text
I want to copy the URL of the hyperlinks stored in the FileN fields to the URLText fields. However, when I run an update query I am getting the full hyperlink code and not just the address.
Example: FileN will appear as a hyperlink like JR1020.pdf, which is obviously formatted as JR1020.pdf#URL#..How can I copy just the plain URL (the stuff between the hash marks) without the hyperlink text names?
Code:
UPDATE [Attachments Table] SET [Attachments Table].URLText = [Attachments Table].[FileN];
I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.The part that makes it slow looks like this -
WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...
If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.
Need to track donations in two areas. Have one query that tracks them in location A and one that tracks them in location B. Same query, no other difference. Report made on location A is fine. Made a copy of it and based it on the 2nd query--and I get a pop-up "Enter Parameter Value" for Last Name. The Last Name field is not in the query or anywhere in the report that I can find.
View 3 Replies View RelatedWhat's the best way in an update query, using access 2003, to update data in a record based on the previous record having an identical value in a certain field?
For example my data looks something like this, in this example I want row 2 to fill in the blanks with the data from row 1 as they both have the same ID number at the end highlighted in red:
1 2 Anywhere Street 1 2 1 1 5423
2 4 Anywhere Street . . . . 5423
3 6 Anywhere Street 1 2 1 1 6401
4 8 Anywhere Street 1 2 1 1 6402
I've found various threads here on how to copy the whole record but I only want to copy the data from certain fields. More oftem than not the fields I'll be copying to will be NULL.
how to reference a field from the previous record within an update query I could probably work it out from there?
I have a table that has a date column. I am assigning one group of students a test date of 4/29 and another group 4/30. I don't want access to change the column or ask for a parameter during the query, but no matter what I do, it keeps asking me for a parameter. I just want it to use the data that is already in the column.
I tried changing the column name in Excel to DOT from Date. That didn't work.I also tried changing the column to text, so that it wouldn't be a "date." That didn't work.
I have multiple queries that I need to have filered by the same parameter (Client). There is a list of clients that receive this report (the report has a pre-made template with multiple tabs, hence the multiple queries).
Currently the user of the database has to filter the exported excel file for each client re-save and email basied on the client. The above is done when the user pushes the specified button on a form (which just says run report a on it).
I am trying to figure out how the write a code so that a parameter (or table if that would work better) pulls the first record of the Client query to filter the other queries being run. Then to loop that process with the second record and so on through the Client query until all the clients have had the report run for them.
Attached are the current VBA being used and the current SQL for the first query that needs the parameter value added.