how to get data from form/table into query

[Elsie]

supposing if I have a form to capture 3 dates: Current Month, End of
Previous Month and Start of Next Month

For Current Month: there are 2 drop-down lists, 1 for year and 1 for month
For End of Previous Month and Start of Next Month : there are 3 drop-down
lists, 1 for year, 1 for month and 1 for day

I tried to use Forms!<FormName>!<ComboName> in the query. this works, ONLY
if the form is open. if the form is closed, the query can't run. how do I
solve this?

Current Work Flow:

once the user selects the starting, ending and current dates and clicks OK,
all 3 dates will be saved to a table. a module will run to do some
processing and table creation. once all this processing is completed,
another form will open to let the user select which report he requires.

I tried to use TableName!ColumnName1 &"/"& TableName!ColumnName2 &"/"&
TableName!ColumnName3 in the query. but it does not work.


Elsie

 

[Elsie]

hi, on start of the database, a form is used to capture date range entered
by the user. my question is: how do I get this date range into the criteria
row of a query, so that I can query for transactions ranging between this
date range?


Elsie

 

[GVaught]

The easiest way is to create a query using all the fields needed from the
data table. Add an entry to the criteria section under the field with the
date information. Type the following: Between[Enter Start Date] AND [Enter
End Date]. Save the query. Attach this query to a form that represents the
data you wish to show. Depending on how you structure the query, the data
returned to the form may or may not be editable.

When the user calls the up the form they will prompted to enter the date
range and the data will be returned to the form. I think this would be
easier than creating a form to enter dates and then write code to write this
info into query.

 

[Van T. Dinh]

If you meant you have 2 TextBoxes on the Form that the
user enters the Start Date and End Date, then you can use
the full references to these TextBoxes as the Parameters
in your Query. For example, you can use something like:

BETWEEN Forms!YourForm!txtStartDate
AND Forms!YouerForm!txtEndDate

as the criterion in your Query.

HTH
Van T. Dinh
MVP (Access)

 

[Elsie]

Well, all you really need is a combo box for the year and the month -
the end of month and start of month can be calculated from these
values.

How do i calculate the end of month and start of month in an effective and
easy to understand way?

By running the query (if you must "run" the query at all, it's
generally a bad idea to open query datasheets) from a command button
on the form. If you're displaying data to the user, the button would
just open a form to display the data.

What i meant by 'run' the query is to process it without opening the query
datasheets. its just for background processing. once the query get the
required data, the results are output into a report.

currently for Forms!<FormName>!<ComboName> to work when used in my query,
the form must be open. why must it be so? is it possible to get the data
from the combo box if the form is closed?

It sounds like you're going to a GREAT DEAL of extra effort! Are you
making the common but incorrect assumption that you must have a Table
containing the specific data needed for a report in order to generate
the report? You don't; it's perfectly possible to base a Report on a
query, which can reference a Form for its criteria. MakeTable queries
are very rarely necessary.

this portion of the whole processing is designed by another person, i am not
sure why he used make-table queries. since i'm a newbie, i try to understand
the whole flow first.

I tried to use TableName!ColumnName1 &"/"& TableName!ColumnName2 &"/"&
TableName!ColumnName3 in the query. but it does not work.

This expression returns a Text String. A Date is NOT a text string -
it's stored internally as a number. Furthermore, you cannot refer to a
table using the TableName! syntax.

You can extract a date value directly from the three combo boxes on
your Form; in fact, if you had only combo boxes for the year and
month, you could use the following criteria for the Current month:

BETWEEN DateSerial([Forms]![yourform]![cboYear],
[Forms]![yourform]![cboMonth], 1) AND
DateSerial([Forms]![yourform]![cboYear], [Forms]![yourform]![cboMonth]
+ 1, 0)

what do these sentences mean: -
[Forms]![yourform]![cboMonth], 1 and,
DateSerial([Forms]![yourform]![cboYear], [Forms]![yourform]![cboMonth]

+ 1, 0?

 

[John Vinson]

supposing if I have a form to capture 3 dates: Current Month, End of
Previous Month and Start of Next Month

For Current Month: there are 2 drop-down lists, 1 for year and 1 for month
For End of Previous Month and Start of Next Month : there are 3 drop-down
lists, 1 for year, 1 for month and 1 for day

Well, all you really need is a combo box for the year and the month -
the end of month and start of month can be calculated from these
values.

I tried to use Forms!<FormName>!<ComboName> in the query. this works, ONLY
if the form is open. if the form is closed, the query can't run. how do I
solve this?

By running the query (if you must "run" the query at all, it's
generally a bad idea to open query datasheets) from a command button
on the form. If you're displaying data to the user, the button would
just open a form to display the data.

Current Work Flow:

once the user selects the starting, ending and current dates and clicks OK,
all 3 dates will be saved to a table. a module will run to do some
processing and table creation. once all this processing is completed,
another form will open to let the user select which report he requires.

It sounds like you're going to a GREAT DEAL of extra effort! Are you
making the common but incorrect assumption that you must have a Table
containing the specific data needed for a report in order to generate
the report? You don't; it's perfectly possible to base a Report on a
query, which can reference a Form for its criteria. MakeTable queries
are very rarely necessary.

I tried to use TableName!ColumnName1 &"/"& TableName!ColumnName2 &"/"&
TableName!ColumnName3 in the query. but it does not work.

This expression returns a Text String. A Date is NOT a text string -
it's stored internally as a number. Furthermore, you cannot refer to a
table using the TableName! syntax.

You can extract a date value directly from the three combo boxes on
your Form; in fact, if you had only combo boxes for the year and
month, you could use the following criteria for the Current month:

BETWEEN DateSerial([Forms]![yourform]![cboYear],
[Forms]![yourform]![cboMonth], 1) AND
DateSerial([Forms]![yourform]![cboYear], [Forms]![yourform]![cboMonth]
+ 1, 0)

 

[John Vinson]

Answers inline.

How do i calculate the end of month and start of month in an effective and
easy to understand way?

If you have a combo box cboYear and a combo box cboMonth, you can use
the DateSerial function to calculate the end of the month and the
start of the month:

DateSerial(Forms!yourform!cboYear, Forms!yourform!cboMonth, 1) = start
DateSerial(Forms!yourform!cboYear, Forms!yourform!cboMonth + 1, 0) =
end

It needn't be understandable to the user because the user never needs
to see this. It's automatic.

What i meant by 'run' the query is to process it without opening the query
datasheets. its just for background processing. once the query get the
required data, the results are output into a report.

Is it an Action query? i.e. a make table or Append query? If so, *it
may not be necessary at all* - you can create a Report based *DIRECTLY
ON A SELECT QUERY*. You don't need to open it in datasheet view, or
run it - simply use the Query as the Recordsource for your report, and
put a button on the Form to open the Report. The report will call the
query, the query will reference the form controls, and your report
will print - no additional steps are needed.

currently for Forms!<FormName>!<ComboName> to work when used in my query,
the form must be open. why must it be so? is it possible to get the data
from the combo box if the form is closed?

It's a bit like "why can't I read this book just because the book
covers are closed"? If the Form is not open, *there is no data for the
query to get*.

this portion of the whole processing is designed by another person, i am not
sure why he used make-table queries. since i'm a newbie, i try to understand
the whole flow first.

I'm suggesting that you *question* and if possible *change* the
workflow. Unless something is going on which I don't know about (which
may indeed be the case) it sounds like the workflow was designed by
someone familiar with dBase or SAS or some other software, for which
creating a new table is routine. It can be done that way in Access but
it's not the best way, and only occasionally even a decent way.

You can extract a date value directly from the three combo boxes on
your Form; in fact, if you had only combo boxes for the year and
month, you could use the following criteria for the Current month:

BETWEEN DateSerial([Forms]![yourform]![cboYear],
[Forms]![yourform]![cboMonth], 1) AND
DateSerial([Forms]![yourform]![cboYear], [Forms]![yourform]![cboMonth]
+ 1, 0)

what do these sentences mean: -
[Forms]![yourform]![cboMonth], 1 and,
DateSerial([Forms]![yourform]![cboYear], [Forms]![yourform]![cboMonth]

+ 1, 0?

They are three arguments to the function named DateSerial. This
function takes three integer arguments, a year, a month number, and a
day; and it returns a Date/Time value. That is, the expression

DateSerial(2004, 6, 19)

returns today's date; if you use the name of a control that contains
an integer number 2004 and/or 6, instead of the literal numbers 2004
and 6, you get the same result: today's date.

The last day of the month is a bit trickier. Let's say cboYear is a
combo box with 2004 in the selected row, and cboMonth a combo with 6.
The expression translates to

DateSerial(2004, 6 + 1, 0)

which "means" the zeroth day of the seventh month; since this is the
day before the first day of July, it is correctly interpreted as the
last day of June.