Re: Need to add date to a form to use for exporting records

[John Vinson]

On Sun, 27 Nov 2005 14:00:02 -0800, "sgharris49"
<sghar...@discussions.microsoft.com> wrote:

>I need to create a form that allows a user to choose a date or range of dates
>that will be used to select records from the Access database to export to a
>tab delimited file. I have a macro already to export the records, but it
>exports the entire database. I need to allow the user to specify a range of
>dates so I can select records to export. It will usually be one day's worth
>(i.e. from 11/27/2005 thru 11/27/2005) but would like to use a range in case
>multiple days would ever need to be exported. I am also browing this site
>for examples. Thanks for any adivce in advance.

Use a Form - let's call it frmCrit - with two textboxes, txtFrom and
txtTo. You can set the default to whatever you like.

Create a Query based on your table, with criteria

>= CDate([Forms]![frmCrit]![txtFrom]) AND < DateAdd("d", 1, CDate([Forms]![frmCrit]![txtTo]))

on the date/time field - the cdate and dateadd stuff are "belt and
suspenders" to a) be sure the user's input is interpreted as a date
and b) get all of the records on the last day of the range if the
table field contains a time component.

Export from this Query rather than from the table directly.

John W. Vinson[MVP]

[Tom Wickerath]

Hi sgharris,

Here is a simple example that you can follow, using the Northwind sample
database. This sample is designed to export order information for the
selected date range. If you have additional criteria, then you'll likely want
to use a more sophisticated Query by Form (QBF) technique, where you create
the WHERE portion of a SQL statement on-the-fly.

1.) Create a new unbound (ie. not based on a table or query) form. Display
the Properties window (View > Properties) if it is not currently displayed.

2.) Add two textboxes to the form. Name them txtStartDate and txtEndDate.
Names are assigned by selecting the control in question, and then entering
the name on the first line shown on the Other tab of the Properties view.

3.) Add a default for each textbox on the Data tab: =Date(). Save the form
with the name: "frmSimpleExport" (without the quotes).

4.) Create a new query that references your new form. Dismiss the add table
dialog without adding any tables. In query design view, click on View > SQL
View. Copy the following SQL (Structured Query Language) statement (Ctrl C)
and paste it into the SQL window (Ctrl V):

PARAMETERS
[Forms]![frmSimpleExport]![txtStartDate] DateTime,
[Forms]![frmSimpleExport]![txtEndDate] DateTime;
SELECT Customers.CompanyName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate)>=[Forms]![frmSimpleExport]![txtStartDate]
And (Orders.OrderDate)<=[Forms]![frmSimpleExport]![txtEndDate]))
ORDER BY Orders.OrderDate;

Save the query as "qryExport".
Note: The Parameters portion of the SQL statement is required if you ever
want to use this query as the source for a crosstab query. It's a good
practice to add parameters. This in done in query design via the Query >
Parameters... menu.

5.) Add a command button to the form, without using the wizard. Name the
command button "cmdExportTabDelim". Save the form.

6.) Create an Export specification named "TabDelimExport". Use this KB
article as a guide:
ACC2000: How to Create and Save Data Export Specifications
http://support.microsoft.com/?id=209740

Save a temporary version of your query that does not include any criteria as
a new query:
qryTemp

SELECT Customers.CompanyName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate)>=[Forms]![frmSimpleExport]![txtStartDate]
And (Orders.OrderDate)<=[Forms]![frmSimpleExport]![txtEndDate]))
ORDER BY Orders.OrderDate;

Step 2 of the KB article reads as follows:
"In the Database window, click Tables (under Objects), and then click
Categories."

Change this to read:
"In the Database window, click Queries (under Objects), and then click
qryTemp."

Follow the rest of the instructions to save your export specification, using
the name "TabDelimExport". (If you use a different name, then make a note of
it so that you can correctly refer to it in the VBA code shown in step 8,
below.

7.) With the command button selected, click on the Event tab of the
Properties dialog. Click into the "On Click" box. You should see a dropdown
arrow. Select "Event Procedure". Then click on the ellipses button (the
button with the three dots on it). This should open a new code window. You
should see the following four lines of code:

Option Compare Database
Option Explicit

Private Sub cmdExportTabDelim_Click()

End Sub

Note: If you do not have Option Explicit, then add it. I recommend visiting
this page to read why you want to have this line of code, and how to ensure
that it will be added to all new code modules:
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

8.) Insert the following code in-between the Private Sub
cmdExportTabDelim_Click() and the End Sub. The result should look like this:

'****************Begin Code********************

Private Sub cmdExportTabDelim_Click()
On Error GoTo ProcError

Dim strPath As String
strPath = CurrentProject.Path

DoCmd.TransferText TransferType:=acExportDelim, _
SpecificationName:="TabDelimExport", _
TableName:="qryExport", _
FileName:=strPath & "\Orders.txt", _
HasFieldNames:=-1

MsgBox "The selected orders have been exported " _
& "to the file Orders.txt" & vbCrLf _
& "in the folder:" & vbCrLf & strPath, vbInformation, _
"Export Complete..."

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in cmdExportTabDelim_Click event procedure..."
Resume ExitProc

End Sub

'****************End Code**********************

Notes:
Use HasFieldNames:=-1 (True) or HasFieldNames:=0 (False) depending on
whether or not you want field names included.

The text file is created in the same folder that the Access application is
found in. CurrentProject.Path is valid for Access 2000 and later. If you are
using Access 97, then there are alternate methods of obtaining the path to
the .mdb file.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Tom Wickerath]

PS. I forgot to include the "belt and suspenders" stuff that John Vinson
included in his post. The DateAdd function is required if your date values
include a time component other than zero (midnight).

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[sgharris49]

Thanks so much for the information. I used your write-upexactly as you laid
it out and the export is working fine. I visited your website and noticed
that you mentioned a kidney foundation for contributions, and I will
definitely make one. This form is something that I needed to build very
quickly and you told me everything I needed to know. Thanks again.

[Tom Wickerath]

Hi Sgharris,

I'm glad I could help. Thanks for letting me know that you had success.

And a BIG THANK YOU for your willingness to make a contribution to the
Northwest Kidney Center. This is really appreciated.