I have a multi-select list box that runs a "For intCounter" and builds a sting into a text box. That works fine the text box will populate as designed <"Closed" OR "On-Going">.
(I know that I should be dynamically creating the query in the first place ... normally I would ... but this is a quick fix that I want to get it into an existing application, while the redesign requirements are being written.)
Now I want to pass that string to a pre-existing query, but I'm not sure what to write in the criteria section.
I've tried...
Like "*" & [Forms]![ViewReports]![TextPickList] & "*"
IIf([Forms]![ViewReports]![TextPickList]="","",[Forms]![ViewReports]![TextPickList])
and just plain old ...
[Forms]![ViewReports]![TextPickList]
but my query is coming up blank. What do I need to write into the criteria section of this saved query?
How can I pass two (2) values to a saved query ? These values are in a form that has a listbox with two (2) columns. The name of the form is 'Previous Evaluation Form'. I'm able to retrieve the values from both columns of the listbox in the form and I've already created the query. Both are working fine, but can figure out how to pass the query's criteria to select records for 'Name' and 'Date' columns of the query. Below is what I had in the 'Criteria:' of the query. What wrong with the code that is placed in the query's 'Criteria:' ?
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?
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.
For whatever reason when I try to add up two columns in a query instead of adding up the two numbers it displays it as a text string. So if one column has a 5 and the other is a 2 I am looking for the calculated column to reflect 7, currently it is showing 5,2.
I have added up items in the past so I am unsure what the problem is
Been a while since I put hands on access. Working with a very simple database.
Working with 1 table, 1 form, 1 query.
Query has several fields. Field of interest contains names of counties.
In query design view I can type, "CountyA" Or "CountyB" or "CountyC" in the criteria and the desired results are returned.
On form I've created a text box and an open report button. The report I'm trying to open uses the query as the data source. I reference the text box on the form as the criteria for the query as follows:
If I enter the the name of a single county into the form it executes perfectly. If I try to enter multiple counties it fails. This is true whether I just enter the county names or replicate the exact criteria string I use in the source query. For example
CountyA works and returns desired values for County A CountyA Or CountyB or CountyC fails. The report opens but no records are returned "CountyA" Or "CountyB" or "CountyC" = exactly how it is entered in the query if I'm not using the form opens report but no records are returned
How can I pass multiple values from a single text box to the query.
While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.
I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"
Is presented in Group by as: "砅5"
I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.
I have designed a database that has two forms as inputs to a table. The first form is a checklist and when it is completed it saves all fields except the ManagerID field. I then use the blank ManagerID, clientID and Date to pull onto a form for the manager to complete. On completion I want the ManagerID to save into the current records so they do not show up in the manager checklist forms and I then have a complete record. I have been searching online and cant seem to see how the best avenue is. I have an append query, see below
Code: INSERT INTO ChecklistResults ( ManagerID ) SELECT ChecklistResults.ManagerID, ChecklistResults.ClientID, ChecklistResults.DateCompleted FROM ChecklistResults WHERE (((ChecklistResults.ClientID)=[Forms]![TeamLeader]![ComClientNotFin]) AND ((ChecklistResults.DateCompleted)=[Forms]![TeamLeader]![ComDateSelect]));
Code: Private Sub CmdAppend_Click() Dim dbsNorthwind As dao.Database Dim rstAmend As dao.Recordset Dim qdfAmend As dao.QueryDef
I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.
If Me.PaNumber = "*D" Then Me.txtMaxOrdLimit.Visible = True Else Me.txtMaxOrdLimit.Visible = False End If
Hi--I am working on a database which is a modification of the following MS Access 2003 Order Management template: (URL address blocked: See forum rules)=CT101426031033
In my database, I have a subform called sbfOrderDetails, which in its Form Footer has a text box that sums the items in the order and is called OrderDetailsTotal.
I am trying to pass that value to a text box in the main form called OrderSubtotal.
I have three questions:
1) What is the proper syntax to use to get the value of the subform control into the OrderSubtotal control on the main form? I have tried various permutations and continue to get a #Name? error.
2) If you look at the expression in the Order Subtotal control of the Add and Manage Orders form in the original Order Management template, it looks like this:
=[Order Details Subform].Form!OrderDetailsTotal
When I try to replicate that in my database, I cannot--if I add brackets around my subform name and press Enter, Access adds brackets around everything in the expression, i.e. =[sbfOrderDetails].[Form]![OrderDetailsTotal] (which returns a #Name? error). If I edit out the brackets I don't want and then press Enter, it puts the brackets back in. In other words, Access keeps trying to "fix" my expression by either having brackets around each item, or no brackets at all! This is really annoying; is there any way to turn it off??
3) When sbfOrderDetails is set to Single Form view, I can see the Form Footer (and the value in the OrderDetailsTotal text box as well). When it is set to Datasheet view, I cannot. Why?
Thank you very much, lcnlit
PS>I cannot attach my database since I am a new poster, and the forum is disabling the URL of the Microsoft template as well.
I am using MS Access 2010 to export data into MS Excel 2010 spreadsheets.
I am just wondering: Is there a way to set the Parameter value for the query via VBA?
For example: There are 10 regional managers. When I click a button on a form, 10 sets of data per manager are going to be exported into Excel spreadsheets.
I have created a saved query named [For exporting] with a parameter [Manager Name] for the field [Master Table].[Manager].
The VBA for the button has 2 subs: 1) Sub 1 for the loop for 10 managers
Code: ... strQuery = "SELECT DISTINCT [Master Table].[Manager] FROM [Master Table] WHERE ((([Master Table].[Manager]) Is Not Null));" Set rstStores = CurrentDb.OpenRecordset(strQuery)
How do i make my subfrm record source switch to another saved query (qryPendingStatus) when it's opened from the form.
Heres what i am trying to accomplish: I have a subfrm that i'd like to use to display info in 2 different forms.
There's a form currently for All statuses. Then another new one for Pending status. I'd like to use the same subform for this pending status form also, and in doing that my qryAllStatuses, but change to qryPendingStatus on the subfrm when i open the mainfrom.
This is on the open even ofthe mainform, but does not work (says method not founds...so obviouly i can't use ".Recordsource" here: Private Sub Form_Open(Cancel As Integer) Me.subfrmJobInfo.RecordSource = "qryPendingStatus" End Sub
My issue is about a saved query which I would like to emulated with VBA.
The whole idea is to update a set of a table's fields based in the status of a control at a form; when the condition is met, the query takes the value of another field for doing the updates.
The original saved query is:
UPDATE EntradaPatiosDetalle SET EntradaPatiosDetalle.precio = IIf([Forms]![EntradaPatios]![patioCheckBox]=True,[precio_patio],[precio_pyme]) WHERE (((EntradaPatiosDetalle.idEntrada)=[Forms]![EntradaPatios]![idEntrada]));
The closest approach I can get to at VBA (which fails ), is:
I've got a field called [WordLink] on a form where the user enters the file name of the Word or Excel document asociated with the record.
I have a button that opens Word or Excel and then opens the file...not a problem there as I'm using the Call Shell("""C:Program Files command and that works just fine.
However, I need the button to look to see if it's got ".doc" or ".xls" within the text string in the field so it knows whether to open Word or Excel.
I've tried something basic to launch something like a msgbox (see code below) but obviously it doesn't work. How do I look within a text string for a value?
Cheers,
Russ :D
If Me.WordLink_filename.Value = "*.doc" Then MsgBox "This is a word file." Else End If
I have created a report that has string text from a query I created. The query, with the string text, take the information from a table. When I run the report the string text gets cut off (The box is big enough to hold the information). The table I have is a linked table to a text file that gets imported from a program. I try creating the same table but not having it linked and the string text worked.
Is there some way I can get around this? Is there something in the linked table that is stoping the string text?
Access 2000. I have table of many hundreds of records. One of th fields of text looks like this: XXXXXXXXXXXX and I want it to look like this: XXX XXX XXX XXX by adding the spaces. Would someone please show me how to write the function or code to add the spaces to the text as shown : Thanks :o
First of all I'm not very good with VB. What I'm trying to do is get all my customers email addresses in one text string. So it would go like 123@aol.com; bob@yahoo.com; fred@hotmail.com; and etc. My customer could then copy it and paste it in her TO: box of her email server. Any help would be great.
I have a text field with receipt numbers in the format 0001-00000### and I would like my data entry form to default the max existing value + 1.
If max value is 0001-00000201, then the new record should suggest 0001-00000202.
The problem is that if I use the "max" function, it does not work (I think because that function is intended for numbers, not text strings).
Table: RECEIPTS Field: receiptnum
I also have a query with just one field that lists only the receiptnum unique values so that I can use them in comboboxes in other forms... it may be useful I guess...
I have saved query object named qrySearchBill. I wan to call this query through vba and display the result in a subform named subQrySearchBills in datasheet view. Here's how I want it to work:
When the main form loads, I want all unfiltered records to be displayed in the subform initially. The user may then decide to filter based on date range, so he enters startdate and enddate parameter values in their respective textboxes in the main form. Then click search button to run the saved query based on the date range parameter taken from the textboxes.
I have this code so far:
SQL of the saved query object:
Code: PARAMETERS [StartDate] DateTime, [EndDate] DateTime; SELECT tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date, Sum(tblInvoice.[TotalPrice]) AS Amount FROM tblCrdCustomer INNER JOIN tblInvoice ON tblCrdCustomer.IDNo = tblInvoice.NameID WHERE tblInvoice.Date Between [StartDate] And [EndDate] GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date;
vba code to call the query and its parameter:
Private Sub btnSearchBill_Click() Dim qdf As DAO.QueryDef Dim rst As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("qrySearchBills")
[Code] ...
This code works fine except that when the main form loads, a prompt window appears to ask for the value of dateStart and dateEnd. I don't want it to prompt because it's suppose to get these values from the main form's textboxes (txtStartDate and txtEndDate respectively), plus it should initially display all the unfiltered records.
Thank you for taking the time to look at this Question.
I am using a form in which i only need the last sections.
example:- G/001 - Ceiling - Tiles - Perforated. this is what i get at the second, but i would like to drop the "G/001 - " and just have "Ceiling - Tiles - Perforated.", has anybody got any ideas on how to do this please.
looked around but could not find the answer. i have a text box that might or might not contain text and number data. an example of the data in the text box might be;
bob roberts, 17/03/2006, aged 23
what i would like is some VB code that would:
check anywhere in the text box for number characters that are 8 characters in length with a comma at the end [12345678,] as in the first example. and change this to 12/34/5678,
bob roberts, 17032006, aged 23 would become 'on lost focus' bob roberts, 17/03/2006, aged 23
I have a text field in a Table and on a Query called "Notes" In that field that has data like below:
[04/02/2015:BD] Project is to be assessed by Solutions Planning [03/27/2015:BD] Project prioritized [03/14/15:BR] Entered to system
Im trying to find a way to pull just the most recent line of text, in this case
[04/02/2015:BD] Project is to be assessed by Solutions Planning
into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.
