I've been searching for several hours now and can't find the answer to this problem...
I'm using a form to capture criteria for a query. One of the fields is a text field and here is the parameter I'm using:
Nz([forms]![frmMain]![Company],[CNm])
I want to return all records if the text box named Company is null. I'm getting only the non-null records. How can I get it to display all the records in the field, CNm?
Thanks!
SELECT tblClient.FullName, tblService.Funding_Source, tblService.Service_Name, tblService.Auth_Date, tblService.Auth_Units, Sum(tblServiceLog.Service_Units) AS SumOfService_Units, [Auth_Units]-[SumOfService_Units] AS [Remaining Units] FROM (tblClient INNER JOIN tblService ON tblClient.SSN = tblService.Client_ID) INNER JOIN tblServiceLog ON tblService.Service_ID = tblServiceLog.Service_ID GROUP BY tblClient.FullName, tblService.Funding_Source, tblService.Service_Name, tblService.Auth_Date, tblService.Auth_Units, [Auth_Units]-[SumOfService_Units];
Ok, this query works fine when it comes to the data it outputs, but everytime I run it it wants to know a "Paramemter Value for SumofService_Units". No matter what number I put in there it still gives me correct data. Why is this box appearing and is there anything I can do to get rid of it?
I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.
Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String Public Function returnName() As String If IsNull(gbl_loginName) Then returnName = "test" ' dummy account created for development only Else returnName = gbl_loginName End If End Function
and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name], [Entry of Hours].[Set Up Time], [Entry of Hours].[Run time], [Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date], [Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,
[Code] ....
when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name
Is there a problem with using public functions in Access' design view?
I have a report based on a crosstab query with a subreport based on a second crosstab query both the crosstab queries have the same 3 parameters (2 as string, 1 as boolean) which I pass through a dialog form that closes after the report is opened (in preview).looks like everything works fine at least for the first page of the report but when I try change page, it prompts me to re-enter the parameters. Of course, if a do not close the parameters form I will not have this problem but that is a popup dialog which I do not want to close manually after the operation is done
So I tried to pass the form input first to a Public variable then to a Public Function but I was not able to reference neither of them in the PARAMETER statement of the 2 Xtab queries..As a function, each of the following declarations prompts for input at runtime::
MyFunction, [MyFunction], [MyFunction()], [=MyFunction()] while the following are not accepted (syntax error) MyFunction(), =MyFunction()
I've a method **querylistboxitems** and i want to call this method in several click events, only difference is listbox,dropdown values change based on the event i call.
Code:
Public Sub querylistboxitems(lstbox As listbox, dropdown As ComboBox) Dim drpdwnvalue As String drpdwnvalue = dropdown.Value With lstbox //do something End with End Sub
And I'm calling this in the buttion click event by passing the listbox names as **List_Compare** and **Select_CM_Compare**
Code: Private Sub Command_compare_Click() Call querylistboxitems_1(List_Compare, Select_CM_Compare) End Sub
But the values passing to the function are not control names, control values i.e corresponding control selected values. I want to use listbox name in **lstbox**, not the value.
I have a parameter query in which consists of a search statement as below:-
Search: [Model] & " " & [FiscalYear]
It works very well. I am wonder if there is any options available to affect the search results. As far as I understand, when I enter the key word into the pop up box, it will search the fields sequentially and populate the result. Can I enter different key word for each field? If so, what is the syntax for entering the key words.
I have 4 fields that are unbound on a form. img1 img2 img3 img4..When these are entered they are all combined and autofill another unbound textbox = imagename.. what i would like to do is from this unbound textbox 'imagename' ..i would like to populate a textbox that IS bound called FileName
[Event Procedure] Private Sub imagename_Click() Me.imagename = Me.FileName End Sub
Hi guys! I'm working on a project where I need to be able to use 4 different text boxes as parameter query. I need to use this boxes dynamically, could be text1 and text2 and text 3, or could also be text1 to text4, and/or text1 and text2. Here is the code I'm using right now, but it is not working. I placed this in the Criteria for field EmpID
I want to create a query where the field being reference is an employee ID in a purchase orders table.
I have a sperate employee table with all the employees in and the employee ID in the other tables is set so that it displays the name in the forms.
I would like to set the query up so that when i type the name into parameter query or search form it is not looking for the employee ID number but the employee name that is typed?
I 've created a query that copies the records of a table into the same table, creating new records. (I don't know how this is called in english, sorry). For example if have a table with 2 records when the query is run I have 4 records with duplicates. The fields of each record I want to remain the same except for one, called code. For example: this is the table before the query is run:
surname name code tracy john 1 spencer bud 1
and this is the table after the query is run:
surname name code tracy john 1 spencer bud 1 tracy john 2 spencer bud 2
the code is also in an another table and is included in a textbox in my main form. Everything works fine when I run the query but I must type a parameter (code) to create the duplicated table. How can I pass the parameter to the query "automatically", using the textbox value? Thanx in advance
trying to pass a textbox value as parameter in a sql query.I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.This is an example:The user enter the data:
[Text0] A1C556CC3C-TNNN C010070H13
The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"
Code: test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """" [Forms]![Search]![Text0].Value = test DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly
In the SQL code I use the IN operator to find the exact value for each record:
[code]...
I'm passing the value incorrect into the sql code?
My problem is when I do this I get a pop up asking for a Parameter Value. but there is text in Text89. How to I get a round the Parameter Value box coming up an automatically do this. If I type the first four letters in to the Parameter Value it finds it on Consignment Guide.
I have a report and within the report I have added some text boxes with some simple logic such as sum, count, etc. These text boxes function well as long as the logic is referencing existing fields. The minute I try to have a text box reference another text box I get the "Enter Parameter Value" box pop up. I don't understand this as the text box I am referencing has a vaule based on what it is referencing.
Example: 1st text box control source =Sum([existingfield]) I get a value. 2nd text box control source =[existingfield2]/[1st text box] I get "Enter Parameter Value" of 1st text box.
I am very new to access, any way that I can have a popup box appear when I ask to produce a report and I can type in a parameter of my choice and it will produce a report based around that parameter?
I have a table that is directly linked to an excel spread sheet that is updated each week external to the database. I have to produce reports on the data contained in that table.
I have already produced reports that look for specific number and those reports are produced automatically.
I was wondering if there was some way I could open a form and type a number into a text box and it would produce a report around that number. e.g. "list all engines below X margin" and I can type any number in representing X and a report would be produced.
(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 new to Access and was wondering if there is a function that can split/divide the contents of one field into seperate fields like you can do in Excel via the 'text to columns' function. I have people's names (surname christianname in one field in a table and want to have a query that splits them up by the space between the names and put one in one filed and the other in a seperate field).
txtNotes is a text box on my form. I use this code to put an AutoText into the box: Code:txtNotes = "New Appointment: " & Chr(13) & "Sales Rep.: " & Chr(13) & "Date and Time: " & Chr(13) & "Contact: "
I would expect the text appear in the text box like this: New Appointment: Sales Rep.: Date and Time: Contact:
...but it appears in ONE single line with a square between the 'labels' instead of a CRLF, like this: New Appointment: {square}Sales Rep.: {square}Date and Time: {square}Contact:
I tried it also with Chr(10) but came to the same result. Any idea how to overcome this?
Hi Guys, I am willing to design a form where I could browse the files and a button should read from text file and insert values into my Table. Please help me in this regard.
I want to have a function with parameters like text file name and destination table. Any clue will be highly appreciated
Let me start by saying I am very, very new to Access, but have been assigned a report to create.
I have the following textbox in my report: =[Location]+(Chr(13) & Chr(10)) & [callnumber]+(Chr(13) & Chr(10)) & [Online Availability]+(Chr(13) & Chr(10)) & [PF Subject]
I'm using this format so that my 4 fields appear in a list and don't leave blank lines if the one of the fields happens to be empty.
The problem is that I need to alter or substitute some text.
First, in the location field, I can have several different entries, but if the entry is 'Circ', I don't want that text to display and I don't want a blank line either.
Secondly, in the online availability field, if there is any value in the field, I want whatever text that is to change to something like 'Online - Yes'. If there is no value in the field, nothing should display.
I think the use of the Iif function should work, but I can't seem to write it correctly. Help!!
I have a function in a module that looks like this (it takes 2 dates as arguments):
Code: Function get_KPIScanAgeRange(in_ScanDate As Date, KPIDate As Date) As String Dim ret As String ret = "Invalid" ' return value, by default is because age is negative number
[Code] .....
I'm trying to pass the date in a text box which exists in a form called "d3FormAging". The text box name is "KPIDate". The query looks like this:
Code: SELECT Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]) AS KPIScanAgeRange, Count(Query_d3_Open.[Scan date]) AS Scans FROM Query_d3_Open GROUP BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]) ORDER BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]);
For some reason Access doesn't recognize the "[Forms]![d3FormAging]![KPIDate]" when I pass it to the function. I get run-time error 3070 with information that Access can't recognize this expression even though the code should be correct as it's made with the expression builder and I'm sure the form that contains the text box is open when the query runs.