Hi:
In MS Access:
In the query:
I set a input parameter: [Please enter the date:]
I run it, enter a parameter. The results come out, it is correct.
And then I sort the ascending order of the Vendor Name.
And close the query.
Second time, I run it, It give out 2 times [Please enter the date:]
I need to enter twice input. Why?
How can I solve it?
Please let me know, thanks.
Please can someone tell me how to go about adding to the code below. Currently the user enters the date criteria in a form. This works great and the data is exported to excel. But I can't seem to figure out how to get the input from the user to also be exported to Cell A1 in excel. Example: if the user enters starting date and end date, that information should be placed in the A1field in excel. Thanks for your help.
Public Function ExportDataExcel()
Dim strFilePath As String
Dim strFileName As String
Dim strFileTemplate As String
Dim strMacroName As String
If (MsgBox("You are about to generate the LAR Monthly Report. Are you sure you wish to continue? You cannot cancel this procedure once started.", vbOKCancel) = vbCancel) Then
Exit Function
End If
'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Fill in the following with your files and path
strFilePath = "R:Call CenterCall Center DepartmentsMortgage DeptMortgage Statistics & Tracking"
strFileName = "Output.xls"
strFileTemplate = "Template.xls"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'This deletes the old file
Kill strFilePath & strFileName
'This recreates your file with the template
FileCopy strFilePath & strFileTemplate, strFilePath & strFileName
openexcel strFilePath & strFileName
ExportData "qryHoeqDotApproved", "HOEQ DOT APPROVED"
ExportData "qryHoeqDotReceived", "HOEQ DOT RECEIVED"
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
xl.ActiveWorkbook.Save
'The Application.Run will run the Macro(s) that you saved in your spreadsheet
xl.Application.Run "'" & strFileName & "'!" & strMacroName
xl.ActiveWorkbook.Save
'Uncomment/Comment these to close out the workbook
xl.ActiveWorkbook.Close
xl.Quit
DoCmd.Close acForm, "frmLar"
Set xl = Nothing
End Function
Private Function ExportData(strQuery As String, strSheet As String)
Dim intR As Integer
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")
'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will
'use it later, but now you have to access your queries through this code and to do so
'you need to use a recordset.
'strQuery is the name of the Query that you passed with the Function. You can also
'use an SQL string.
Set dbs = CurrentDb
'QueryDefs (0)
'QueryDefs ("name")
'QueryDefs![name]
Set qd = dbs.QueryDefs("" & strQuery & "")
qd.Parameters![txtStartDate] = [Forms]![frmLar]![txtStartDate]
qd.Parameters![txtEndDate] = [Forms]![frmLar]![txtEndDate]
Set rs = qd.OpenRecordset
'Set rs = CurrentDb.OpenRecordset(strQuery)
rs.MoveLast 'moves to the last record
rs.MoveFirst 'moves back to the first record
'You can use record count to make sure there are records in your Query/Recordset
If rs.RecordCount < 1 Then
'There are no records
MsgBox "There are no records for " & strQuery
Else
'There are 1 or more records. Now Select the sheet that you will be exporting to
xl.Sheets(strSheet).Select
'Now you need to loop through the records. 'intR' was dimmed at beginning of this
'function and will now use it to create a loop or 'For, Next'
'Starts with record 1 and gets the count of records in the recordset so it knows where
'to stop.
For intR = 1 To rs.RecordCount
'Now we need to export the recordset/query to the workbook/object we opened earlier.
'Remember 'rs' refers to the recordset & 'xl' refers to the workbook
'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX).
'This is how you will fill in the value of a cell on the workbook. For the ROW you
'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields
'refers to the columns of the recordset/query. The first column of the recordset
'starts with the index of zero.
xl.Cells(intR + 3, 1).Value = rs.Fields(0)
xl.Cells(intR + 3, 2).Value = rs.Fields(1)
xl.Cells(intR + 3, 3).Value = rs.Fields(2)
xl.Cells(intR + 3, 4).Value = rs.Fields(3)
'Moves to the next record
rs.MoveNext
Next intR 'Loops back to For and enters data for the next row
'Once the export is done, this just puts the cursor to A1 on each sheet
xl.range("A1").Select
'Clears the recordset
rs.Close
Set rs = Nothing
vStatusBar = SysCmd(acSysCmdClearStatus)
End If
End Function
I have a query that has a date/time field (mm/dd/yyyy hh:mm:ss), I have created another field using the following syntax " Sdate: Format([AgentCallDetailInCalls.StartTime],"Short Date") ", I prompt the user for a date using this sdate field I created. The prompt syntax is [Please Enter Start Date]. Currently you have to enter 2/22/2008 (with slashes), I would like to enter 2222008 (without the slashes) to extract the data. The mask I have setup, does nothing. If I put in the date without the slashes, I get nothing, if I put in the slashes, it runs perfectly. Does anyone know a way around this? Thanks in advance for your most knowledgeable response. :confused:
View 1 Replies View RelatedI am not a programmer and I don't know VBA/VB.
I am using this as a workaround to avoid VBA functions (since I don't know them).
I can't seem to find a simple? solution to this.
I have a query that does a radial search in decimal degrees. I have created an unbound form as a dialogue box that converts degrees-minutes-seconds to decimal degrees and displays the results in a calculated text box. What I want to do is have the query take the results in the calculated text box as its parameters (along with a third parameter- distance) without prompting the user. How do I get the query to take its results from the fields on the form? I have tried Like [Forms]![frmName]![SearchValue], but I can't seem to make it work.
I have a report which asks for the beginning date and ending date which they want. The report is generated using this information. I want to put the information entered in the parameter request into the heading of the report. How do I recover this information to put it in.
Jerry Hughes
Every time I run a query that I have created it asks me to input Expr1 and Expr2 in an enter perameter value pop up box. I don't enter anything, just click okay and the query runs as expected.
Is there a way to get rid of these? (and maybe more importantly, why do they appear?)
I'm using an Excel spreadsheet that is importing external data from an access database in which I've got a field where the name of a person checking materials out is entered. it is currently set up, and I cannot change it, as a free form field. So folks enter information in a variety of ways.
For example, Larry Martin might be entered as "Larry Martin" or "Martin, Larry" or "larry.martin@somewhere.com". I'm trying to run a query that would look in that field for any entry with the string I enter, such as "Martin."
I've tried setting the criteria like this:
Like "*" & [Which Last Name] & "*"
However, when I try and run the query I get a message telling me the system is expecting two parameters.
Does anyone have any idea what I'm doing wrong? I've been banging my head against this for awhile now and am thoroughly stumped! All assistance, as usual, is greatly appreciated!
I have a report that has an underlying query that asks for Start Date and End Date.
Is there anyway that I can get what the user inputs into the box to be put into the Page header of the report?
ie: user enters into the parameters
[Start Date] 01/01/2006
[End Date] 04/04/2006
Then when the report displays it says
Report for the period 01/01/2006 to 04/04/2006
Thank you for your time
I have to print a label quickly every time that the product hit the warehouse. The label has been created as a report linked to the query that will provide the info to the report. In order to make this report printing as quick as possible the idea is to scan the sample id from the product and once the label is printed scan the next sample and an on.
I'm not an expert on VBA but I have created the following scrip but the reports doesn't pop up.
Here is the code:
Dim SampleID As String
SampleID = InputBox("Enter Sample ID")
If SampleID > 0 Then
DoCmd.OpenReport "rptGRM_QuickPrintLabelDymo", acViewPreview, , "[Sample]=" & SampleID
Else
DoCmd.Close
End If
End Sub
I have a table which holds information on audits that have been carried out on staff member's actions. The 'Supervisor' field is populated via a combo box which is linked to a separate table (tblSupervisors).
I am now trying to build a query to allow me to extract all audits that have been carried out on a specific supervisor - rather than the criteria to be [Enter Supervisor Name] and allowing text entry, I thought it would be better to have form that pops up with a combo box that is used to select the supervisor (from tblSupervisors);
So far:
- Form "Supervisor_Select" is created, and has a combo box that looks up from tblSupervisors
- Macros as specified in the instructions are created (Open Dialog, Close Dialog, OK and Cancel)
- Query is done, all bar the criteria expression on the desired field.
- Module is created as described in the instructions, and is called "Supervisor_Select"
I have tried putting the following in the criteria;
[Forms]![Supervisor_Select]![cboSupervisor], however I think I am missing the bit where the query opens the "Supervisor_Select" form?? Will this only work from a button where the on click event runs the 'Open Dialog' macro and then runs the query?
I have created a query with the parameter for the Domain field. however on the form the user enters this information via a drop down menu. i was just wondering could the parameter box be set to a drop down box as well to save the user entering in the full Domain field name?
View 6 Replies View RelatedHello,
I need to create a field in an input form that is simply the concatenation of two other text fields. I have tried all sorts of things, but when I look at the data in the table that field.
I have a field called ID that I want to be created like this:
=Format([UniqueID],"00000") & "-" & [Mosque]
This works well in my output fields, but does not work the same way on the input form. It needs to be based on the currently input values from the current record. Anyone have any ideas?
Thanks in advance,
--Robert
I need to be able to query the service dates for the bill. So I can query 1509 for the .billYYMM and .readdate and it will give me the TO Date. How do I in the same query tell the query to subtract 1 from the BillYYMM that I just entered and give me the read date for that field in that query? So Query 1 MBRHISTDETL.BILLMOYR and in the criteria box use [Enter the Bill Month Year YYMM you want to search for] will allow me to run the query at any time for any bill month year that I enter. So another field in that query is MBRHISTDETL.READDATE. So to get the FROM date and the TO date i need the following MRBHISTDETL.BILLMOYR (whatever I enter as input) and MBRHISTDETL.READDATE that corresponds and then I need [MRBHISTDETL.BILLMOYR]-1 and MBRHISTDETL.READDATE. The first gives me the TO date and the 2nd gives me the FROM date?
View 5 Replies View RelatedI have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies View RelatedHi,
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
hi guys
i hvae a situvation that i hvae to take password from the user for that i am using inputbox option to take password. the problem is if i use input box we can't mask the input text box as far i know. so do we hvae any other option without creating another form for that.
thank you
HOW DO I DO THE FOLLOWING:
I want to use input mask in my email field i.e the @ must be present but i must be allowed to input values or numbers before and after the @. This did not worked because i have fixed the values: ????@????
thanks
Dear All,
Is there any way that I can use an input mask to enter serial numbers of softwares.....
the data will be like this...
ABC8F-CHJ68-FH76F-GHF87-67JH5
Thanks in advance
Thanks
I am trying to uses a inputbox. I need it to end the script if cancel is hit, but the value from the input box can be zero? So cant filter it that way.
Also
Does the SQL command LIMIT work in access and is it possible to number row in order?
What I have is field called contract number, and its entered as 09-0011, which is ok.
Now I like it to show up in a different field as 090011. I guess my first question can this be done. Or even better how would I do it?
Now your question is. Why don't you put it in correctly the first time, and the answer is we want the number to have a dash.
Any suggestions
Never can be easy for me.
Does anyone know how to get input from a com port and parse it into access?
View 1 Replies View RelatedHey all, I am currently looking to make a great database system for use within my community. What I currently have is a mdb with just 3 tbls but will be building from there. I have the normalization down thus far, but wanted to put things out there for any suggestions or ideas to make this a useful system. This is not a profit situation, and will generally be used only by myself or possibly one or two others.
What I have is a database that tracks the homeowners within our community (subdivision) and their properties. So far I have 3 tbls
tblOwners
- OPkey (autonumber)
- OFname
- OLName
- OStAddress
- OCity
- OState
- OZip
tblUnits
- UnitPKey
- StNum
- Street
- PIN
- Check2005
tblJct
- UnitPKey
- OPKey
Structurally this works great so far. I can easily relate properties to their owners and link multiple properties to those who own more than 1 (landlords). I structured it this way since the two tbls had many to many relationships, so the junction tbl made the most sense.
Now I am looking to add information from a tract search (mortgage holder information) which technically relates to both of these tbls (Unit & Owner) as well. I already know that most of the owners with multiple properties will most likely have the same mortgage holder, but that is not definitive. Since the same mortgage holder could easily be associated to multiple properties within the community, I know I have to have an independent tbl for that information as well.
What I would like to head for is something that will allow me to send notifications, letters, flyers and the likes to either the owners, residents, mortgage holders, or any combination of the three. I have attempted to create a few thus far but have issues with getting all of the required data output to Word, so I am thinking I may need to design these items in a Form instead.
Any thoughts or comments would be appreciated. I know I am heading in the right direction, but just putting it out here for the Access community to put in their 2 cents.
Dear fellow forum members have been trawling the forum but unable to solve what seems a simple issue.Have a form with a field for a short date input.I would like the year to be input automatically and the user to be left to input day and month.Have played around with format(date(),mm-yy) but unable to crack it.If someone can save me another sleepless night I would be more than grateful.
View 3 Replies View Relatedlet's say i have a field, in which i store and identity card number. This number may consist up to 7 digits (of which 3 are mandatory) plus 1 letter (mandatory) at the end. Thus a valid identity card number may be the following: 1234567M, 123M
Eventually, since the field must always contain a letter, i set the data type to Text with field size of 8 ... and i set the inout mask as follows:
9999000L (since the first 4 digits are mandatory). With this input mask, if i have an ID Number of 123M, i have to input it as 0000123M.
Although, I would like to have the leading zeros, is it possible that during data entry time, i would simply type 123M, and i will get the zeros automatically, after the field loses the focus, rather than having to type them myself ?
Thank You
I want to force user to input state in capital letters, ie; CA, CO, WA, etc., with two text characters only. Working in design view, I used this character: > in the input mask field property area,
which I understand (perhaps mistakenly) will convert characters to uppercase. Now, I cannot enter any info in the state field.
Any help would be most appreciated.
Hi Guys,
I posted on this forum a few months ago chasing a solution and still haven't found something. Is ther any way in vb that you can prompt the user to input a number? I was posted the code below to try and help. In the example below, i would like to be able to have the end user input the first and last numbers (11000 and 11100, in the example) in a pop up box, as these vary, not have them defined in the code, is this possible? In the database, the first and last numbers in a string are all that is needed and the db fills in the gaps, so to speak
Const MyTable As String = "YourTableName"
Const MyField As String = "YourFieldName"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
For intCounter = 11000 To 11100
rs.AddNew
rs.Fields(MyField) = "TP" & intCounter
rs.Update
Next intCounter
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Thanks for any help you can give. I hope it is pretty straight forward, as my vba skills are fairly limited.