Selection Conversion To Query Entity on a Form

  • Thread starter Thread starter Henry Smith
  • Start date Start date
H

Henry Smith

I have a complex form that allows user to make many different selections of
time period and other attributes of a project (such as project number). The
end result of all the selections, when the user selects display button, is a
specific report.

Information:
Source table for underlying data of the report has three fields related to
dates.
Field1 [Book Data], data type = text, format = mm/yyyy, Example: 01/2000 or
11/2000
Field2 [Fiscal Year], data type = text, Example: 2000
Field3 [Fiscal Period], data type = text, Example: 7
My company fiscal year starts 1 July of the year. So January 2001 equals
physical year 2001 with a fiscal period equal to 7.
My form has 4 drop-down combo boxes (cbo) for the user to select the
beginning date and ending date of the report he/she wishes to see.
Beginning date (cbo) (two drop down boxes) have selections, MONTH cbo
displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
2001, 2003, etc.
Ending date (cbo) (two drop down boxes) have selections, MONTH that
displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
2001, 2003, etc.
So values of selected cbo's are, for example; Jan 2000 and Feb 2000.

My question is how do I convert beginning and ending period selections to
the text formatted field [Book Data] of the data source table so I can use
the converted data in an accurate query against my source table.

Any ideas will be greatly appreciated.
Cheers,
Henry
 
Hi Henry,

We can create a function to convert the format. Below is a sample function
for your reference.

Function ConvertYM(strm As String, stry As String)

'Jan 2000
'mm/yyyy, Example: 01/2000 or 11/2000

Dim intm As String

'Chr(47)=/

Select Case strm

Case "Jan"
intm = "01"

Case "Feb"
intm = "02"

Case "Mar"
intm = "03"

Case "Apr"
intm = "04"

Case "May"
intm = "05"

Case "Jun"
intm = "06"

Case "Jul"
intm = "07"

Case "Aug"
intm = "08"

Case "Sep"
intm = "09"

Case "Oct"
intm = "10"

Case "Nov"
intm = "11"

Case "Dec"
intm = "12"

Case Else
intm = "Invalid"

End Select

ConvertYM = intm & Chr(47) & stry

End Function


A sample code to call the function:

Dim stra As String

stra = ConvertYM("Jan", "2003")

MsgBox stra


Please feel free to reply to the threads if you have any questions or
concerns.




Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: "Henry Smith" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| I have a complex form that allows user to make many different selections
of
| time period and other attributes of a project (such as project number).
The
| end result of all the selections, when the user selects display button,
is a
| specific report.
|
| Information:
| Source table for underlying data of the report has three fields related to
| dates.
| Field1 [Book Data], data type = text, format = mm/yyyy, Example: 01/2000
or
| 11/2000
| Field2 [Fiscal Year], data type = text, Example: 2000
| Field3 [Fiscal Period], data type = text, Example: 7
| My company fiscal year starts 1 July of the year. So January 2001 equals
| physical year 2001 with a fiscal period equal to 7.
| My form has 4 drop-down combo boxes (cbo) for the user to select the
| beginning date and ending date of the report he/she wishes to see.
| Beginning date (cbo) (two drop down boxes) have selections, MONTH cbo
| displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
| 2001, 2003, etc.
| Ending date (cbo) (two drop down boxes) have selections, MONTH that
| displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
| 2001, 2003, etc.
| So values of selected cbo's are, for example; Jan 2000 and Feb 2000.
|
| My question is how do I convert beginning and ending period selections to
| the text formatted field [Book Data] of the data source table so I can use
| the converted data in an accurate query against my source table.
|
| Any ideas will be greatly appreciated.
| Cheers,
| Henry
|
|
|
 
Thank you for the function suggestion using select case.
I will try that.

Cheers,
Henry
Alick said:
Hi Henry,

We can create a function to convert the format. Below is a sample function
for your reference.

Function ConvertYM(strm As String, stry As String)

'Jan 2000
'mm/yyyy, Example: 01/2000 or 11/2000

Dim intm As String

'Chr(47)=/

Select Case strm

Case "Jan"
intm = "01"

Case "Feb"
intm = "02"

Case "Mar"
intm = "03"

Case "Apr"
intm = "04"

Case "May"
intm = "05"

Case "Jun"
intm = "06"

Case "Jul"
intm = "07"

Case "Aug"
intm = "08"

Case "Sep"
intm = "09"

Case "Oct"
intm = "10"

Case "Nov"
intm = "11"

Case "Dec"
intm = "12"

Case Else
intm = "Invalid"

End Select

ConvertYM = intm & Chr(47) & stry

End Function


A sample code to call the function:

Dim stra As String

stra = ConvertYM("Jan", "2003")

MsgBox stra


Please feel free to reply to the threads if you have any questions or
concerns.




Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: "Henry Smith" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| I have a complex form that allows user to make many different selections
of
| time period and other attributes of a project (such as project number).
The
| end result of all the selections, when the user selects display button,
is a
| specific report.
|
| Information:
| Source table for underlying data of the report has three fields related to
| dates.
| Field1 [Book Data], data type = text, format = mm/yyyy, Example: 01/2000
or
| 11/2000
| Field2 [Fiscal Year], data type = text, Example: 2000
| Field3 [Fiscal Period], data type = text, Example: 7
| My company fiscal year starts 1 July of the year. So January 2001 equals
| physical year 2001 with a fiscal period equal to 7.
| My form has 4 drop-down combo boxes (cbo) for the user to select the
| beginning date and ending date of the report he/she wishes to see.
| Beginning date (cbo) (two drop down boxes) have selections, MONTH cbo
| displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
| 2001, 2003, etc.
| Ending date (cbo) (two drop down boxes) have selections, MONTH that
| displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
| 2001, 2003, etc.
| So values of selected cbo's are, for example; Jan 2000 and Feb 2000.
|
| My question is how do I convert beginning and ending period selections to
| the text formatted field [Book Data] of the data source table so I can use
| the converted data in an accurate query against my source table.
|
| Any ideas will be greatly appreciated.
| Cheers,
| Henry
|
|
|
Click to expand...
 
Henry said:
I have a complex form that allows user to make many different selections of
time period and other attributes of a project (such as project number). The
end result of all the selections, when the user selects display button, is a
specific report.

Information:
Source table for underlying data of the report has three fields related to
dates.
Field1 [Book Data], data type = text, format = mm/yyyy, Example: 01/2000 or
11/2000
Field2 [Fiscal Year], data type = text, Example: 2000
Field3 [Fiscal Period], data type = text, Example: 7
My company fiscal year starts 1 July of the year. So January 2001 equals
physical year 2001 with a fiscal period equal to 7.
My form has 4 drop-down combo boxes (cbo) for the user to select the
beginning date and ending date of the report he/she wishes to see.
Beginning date (cbo) (two drop down boxes) have selections, MONTH cbo
displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
2001, 2003, etc.
Ending date (cbo) (two drop down boxes) have selections, MONTH that
displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
2001, 2003, etc.
So values of selected cbo's are, for example; Jan 2000 and Feb 2000.

My question is how do I convert beginning and ending period selections to
the text formatted field [Book Data] of the data source table so I can use
the converted data in an accurate query against my source table.
Click to expand...

I think a major part of the problem is that you have text
fields with partial dates. The CDate function will convert
a string that looks like mm/yyyy to a real date value using
the first of the month. E.g. CDate("05/2003") will return
the value #05/01/3004#

The combo box values can also be converted to a date value
using a couple of different ways:

DateSerial(Forms!theform.cboStartYear,Forms!theform.cboStartMonth,1)
or
CDate(Forms!theform.cboStartMonth & "/" &
Forms!theform.cboStartYear)

You can then use thos converted date values in your query's
Where clause
 
Marshall,
Thank you for the suggestion.
Unfortunately, I have to make queries on the table's [Book Date] field with
data type = text. (example entries 01/2001, 07/2001)
I have no control over the source table data types (comes from corporate
financial database as a downloaded, comma delimited, text file which is
imported into AC2000 application table). I use this field to tell the user
the size of the data universe from oldest (i.e., 03/2000) to newest (i.e.,
10/2003) that he has available in the application on his desk top.
There are multiple records for each month. The user only needs to select a
month and year to define the beginning and end of a particular period for
the data he wants. (example cboBeginMonth provides "Jan", cboBeginYear
provides "2001", cboEndMonth provides "Jul", cboEndYear provides "2001") My
query based on that set of selections will give the user a record set of all
records from the source table between 01/2001 and 07/2001. This record set,
with other parameters selected, such as ProjectNumber, becomes the basis for
a set of reports which the user has also selected. The selection form is
quite intuitive and the user only has to make a few clicks to get a report
formatted as he desires.
Changing my present conversion sub procedure to the suggested Function with
the Select Case statement will simplify the code and probably work faster.

Cheers,
Henry

Marshall Barton said:
Henry said:
I have a complex form that allows user to make many different selections of
time period and other attributes of a project (such as project number). The
end result of all the selections, when the user selects display button, is a
specific report.

Information:
Source table for underlying data of the report has three fields related to
dates.
Field1 [Book Data], data type = text, format = mm/yyyy, Example: 01/2000 or
11/2000
Field2 [Fiscal Year], data type = text, Example: 2000
Field3 [Fiscal Period], data type = text, Example: 7
My company fiscal year starts 1 July of the year. So January 2001 equals
physical year 2001 with a fiscal period equal to 7.
My form has 4 drop-down combo boxes (cbo) for the user to select the
beginning date and ending date of the report he/she wishes to see.
Beginning date (cbo) (two drop down boxes) have selections, MONTH cbo
displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
2001, 2003, etc.
Ending date (cbo) (two drop down boxes) have selections, MONTH that
displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
2001, 2003, etc.
So values of selected cbo's are, for example; Jan 2000 and Feb 2000.

My question is how do I convert beginning and ending period selections to
the text formatted field [Book Data] of the data source table so I can use
the converted data in an accurate query against my source table.
Click to expand...

I think a major part of the problem is that you have text
fields with partial dates. The CDate function will convert
a string that looks like mm/yyyy to a real date value using
the first of the month. E.g. CDate("05/2003") will return
the value #05/01/3004#

The combo box values can also be converted to a date value
using a couple of different ways:

DateSerial(Forms!theform.cboStartYear,Forms!theform.cboStartMonth,1)
or
CDate(Forms!theform.cboStartMonth & "/" &
Forms!theform.cboStartYear)

You can then use thos converted date values in your query's
Where clause
Click to expand...
 
Henry said:
Marshall,
Thank you for the suggestion.
Unfortunately, I have to make queries on the table's [Book Date] field with
data type = text. (example entries 01/2001, 07/2001)
I have no control over the source table data types (comes from corporate
financial database as a downloaded, comma delimited, text file which is
imported into AC2000 application table). I use this field to tell the user
the size of the data universe from oldest (i.e., 03/2000) to newest (i.e.,
10/2003) that he has available in the application on his desk top.
There are multiple records for each month. The user only needs to select a
month and year to define the beginning and end of a particular period for
the data he wants. (example cboBeginMonth provides "Jan", cboBeginYear
provides "2001", cboEndMonth provides "Jul", cboEndYear provides "2001") My
query based on that set of selections will give the user a record set of all
records from the source table between 01/2001 and 07/2001. This record set,
with other parameters selected, such as ProjectNumber, becomes the basis for
a set of reports which the user has also selected. The selection form is
quite intuitive and the user only has to make a few clicks to get a report
formatted as he desires.
Changing my present conversion sub procedure to the suggested Function with
the Select Case statement will simplify the code and probably work faster.
Click to expand...

I guess I was a little too circumspect. Use the CDate
function in the query:

SELECT somefield
FROM sometable
WHERE (CDate([Book Data]) Between
CDate(Forms!theform.startmonth & " " &
Forms!theform.startyear) And
CDate(Forms!theform.endmonth & " " &
Forms!theform.endyear))
AND . . .


I have a complex form that allows user to make many different selections of
time period and other attributes of a project (such as project number). The
end result of all the selections, when the user selects display button, is a
specific report.

Information:
Source table for underlying data of the report has three fields related to
dates.
Field1 [Book Data], data type = text, format = mm/yyyy, Example: 01/2000 or
11/2000
Field2 [Fiscal Year], data type = text, Example: 2000
Field3 [Fiscal Period], data type = text, Example: 7
My company fiscal year starts 1 July of the year. So January 2001 equals
physical year 2001 with a fiscal period equal to 7.
My form has 4 drop-down combo boxes (cbo) for the user to select the
beginning date and ending date of the report he/she wishes to see.
Beginning date (cbo) (two drop down boxes) have selections, MONTH cbo
displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
2001, 2003, etc.
Ending date (cbo) (two drop down boxes) have selections, MONTH that
displays Jan, Feb, etc, and YEAR cbo is the year selection such as 2000,
2001, 2003, etc.
So values of selected cbo's are, for example; Jan 2000 and Feb 2000.

My question is how do I convert beginning and ending period selections to
the text formatted field [Book Data] of the data source table so I can use
the converted data in an accurate query against my source table.
Click to expand...
"Marshall Barton" wrote
I think a major part of the problem is that you have text
fields with partial dates. The CDate function will convert
a string that looks like mm/yyyy to a real date value using
the first of the month. E.g. CDate("05/2003") will return
the value #05/01/3004#

The combo box values can also be converted to a date value
using a couple of different ways:

DateSerial(Forms!theform.cboStartYear,Forms!theform.cboStartMonth,1)
or
CDate(Forms!theform.cboStartMonth & "/" &
Forms!theform.cboStartYear)

You can then use thos converted date values in your query's
Where clause
Click to expand...
Click to expand...
 
Back
Top