Okay, here is an example of a case statement that I wanted to you to see
to
help me with this problem:
Function SearchLines(dtype As String)
Dim db As Database
Dim scrit As String
Dim MyMark As String
Dim JobForm As Form
Dim FormName As String
Dim SearchForm As Form
Dim value As Integer
Dim clientcode, Prospect As String
Dim getval As Variant
Dim prevcrit As Integer
Dim linename As String
Dim dateval As Variant
Dim jobtab As String
Dim linetab As String
Dim Querystr As String
Dim SearchQuery As QueryDef
Dim delquery As Integer
Dim I As Integer
Dim Yeartype As Integer
Dim MenuName As String
Set db = DBEngine.Workspaces(0).Databases(0)
MenuName = Screen.ActiveForm.Name
Set SearchForm = Forms(MenuName)
delquery = 0
Yeartype = SearchForm![Yearval]
'MsgBox Str$(Yeartype), 48, "Year"
Select Case Yeartype
Case 95: jobtab = "ATS JOBS 1995"
linetab = "ATS LINES 1995"
Case 96: jobtab = "ATS JOBS 1996"
linetab = "ATS LINES 1996"
Case 97: jobtab = "ATS JOBS 1997"
linetab = "ATS LINES 1997"
Case 98: jobtab = "ATS JOBS 1998"
linetab = "ATS LINES 1998"
Case 99: jobtab = "ATS JOBS 1999"
linetab = "ATS LINES 1999"
Case 2000: jobtab = "ATS JOBS 2000"
linetab = "ATS LINES 2000"
Case 2001: jobtab = "ATS JOBS 2001"
linetab = "ATS LINES 2001"
Case 2002: jobtab = "ATS JOBS 2002"
linetab = "ATS LINES 2002"
Case 2003: jobtab = "ATS JOBS 2003"
linetab = "ATS LINES 2003"
Case 2004: jobtab = "ATS JOBS 2004"
linetab = "ATS LINES 2004"
Case 2005: jobtab = "ATS JOBS 2005"
linetab = "ATS LINES 2005"
Case Else: Exit Function
End Select
prevcrit = 0
scrit = "Select lines.*, jobs.Prospect From ["
scrit = scrit & linetab & "] as lines, ["
scrit = scrit & jobtab & "] as jobs Where "
'MsgBox FORMS![VIEW JOBS]![CURRENT DATE], 48, "DATE"
'Check if client is a search criteria
If (SearchForm![CLIENT CONTROL] = True) Then
getval = SearchForm![CLIENT]
If IsEmpty(getval) Or IsNull(getval) Then
MsgBox "You MUST select a Client if Client option checked!", 48, ""
Exit Function
Else
clientcode = getval
scrit = scrit & "jobs.[Client Code] = '" & clientcode & "'"
' MsgBox scrit, 48, ""
prevcrit = 1
End If
End If
If (SearchForm![PROSPECT CONTROL] = True) Then
getval = SearchForm![Prospect]
If IsEmpty(getval) Or IsNull(getval) Then
MsgBox "You MUST select a Prospect if Prospect option checked!", 48, ""
Exit Function
Else
Prospect = getval
If prevcrit = 1 Then
scrit = scrit & " and "
End If
scrit = scrit & "[Prospect] like '" & Prospect & "'"
' MsgBox scrit, 48, ""
prevcrit = 1
End If
End If
If (SearchForm![LINE CONTROL] = True) Then
getval = SearchForm![LINE]
If IsEmpty(getval) Or IsNull(getval) Then
MsgBox "You MUST select a Line if Line Name option checked!", 48, ""
Exit Function
Else
linename = getval
If prevcrit = 1 Then
scrit = scrit & " and "
End If
scrit = scrit & "[Line Name] like '" & linename & "'"
' MsgBox scrit, 48, ""
prevcrit = 1
End If
End If
If (SearchForm![DATE CONTROL] = True) Then
getval = SearchForm![Date]
If IsEmpty(getval) Or IsNull(getval) Then
MsgBox "You MUST select a Date if Date Before option checked!", 48, ""
Exit Function
Else
dateval = CLng(getval)
If prevcrit = 1 Then
scrit = scrit & " and "
End If
scrit = scrit & "[Date Received] > " & dateval
' MsgBox scrit, 48, ""
prevcrit = 1
End If
End If
If prevcrit = 0 Then
MsgBox "NO Search Criteria were selected!", 48, "WARNING"
Exit Function
End If
scrit = scrit & " AND jobs.[Year] = lines.[Year] and jobs.[Client Code] =
"
scrit = scrit & " lines.[Client Code] and jobs.[Job Code] = lines.[Job
Code];"
'MsgBox scrit, 48, ""
If db.QueryDefs.Count > 0 Then
For I = 0 To db.QueryDefs.Count - 1
' MsgBox db.QueryDefs(I).Name, 48, ""
If (db.QueryDefs(I).Name = "LineSearch") Then
delquery = 1
End If
Next I
End If
If (delquery = 1) Then
db.DeleteQueryDef "LineSearch"
End If
'DoCmd Close A_Form, "Edit Job Info"
Set SearchQuery = db.CreateQueryDef()
SearchQuery.Name = "LineSearch"
SearchQuery.SQL = scrit
db.QueryDefs.Append SearchQuery
If (dtype = "Formview") Then
DoCmd.OpenForm "Line Search Results", A_NORMAL, , , A_READONLY
End If
If (dtype = "Sheetview") Then
DoCmd.OpenForm "Line Search Results", A_FORMDS, , , A_READONLY
End If
Forms("Line Search Results").MenuBar = "Empty_Menu"
'DoCmd Close A_FORM, "Search Lines"
DoCmd.SelectObject A_FORM, "Line Search Results"
'DoCmd RepaintObject A_Form, FormName
End Function
So this is an example of the search form code. Can you give me a full
explanation of what is happening at this point in the process. I think
the
only thing missing here is the code behind the actual search which must be
in
another module or function or whatever you want to call it. Help me again
if
you can.
Edward Letendre.
Ken Snell said:
Answers/comments to your replies:
------------------
"What else I need to know is how does such a function work in a table.
If I
have a column defined as a date and I have the date column setup to
display
the date as dd-mmm-yy and I want to convert the default output to just a
date
(yyyy) only, what should I use as a default date and what format should I
set
the date column as. "
The format of a date/time field does not influence how the data are
stored
in that field. It just controls how the data are displayed. Thus, if you
want a date/time field to show just the year value of the date that is
stored in that field, just set the Format property to yyyy.
Alternatively,
you can use this format for a control (on a form or report) that is bound
to
that field.
The expression that I'd provided in the earlier post ( CurrentYearNumber
=
Year(Date()) ) calculates the year value from the current date. If you
want
to do something similar with the date value from a field, just replace
Date() with the name of the field (if you're doing this in a query):
CurrentYearNumber = Year([DateTimeFieldName])
-------------------
From your description of the ATS_LINES_variable feature, it appears that
you
want the ability to let the user select values that are to be used for
finding specific data, and then the program needs to query your tables to
find the appropriate data. I am still confused by your use of the term
"files" with respect to this -- do you mean tables when you say files? or
do
you mean separate database files that have those names?
Could you post the code that contains the "SELECT CASE" code so that we
can
see what is being done currently?
message Thanks for the reply...as for your response, I added my comments below
to
continue the discussion:
:
Comments inline...
--
Ken Snell
<MS ACCESS MVP>
message Okay, I am in the process of re-creating some modules that were used
in
an
access 2.0 application that was upgraded to access 97 and will
eventually
be
converted to an access 2000 application. The basic problems I am
having
are
as follows:
1. In the code the previous programmer used a function to delete or
remove
a querry to remove it from memmory (I guess) if no results were
returned
(at
least this is my assumption as there is no one to talk to at this
point).
The code used to do this is as follows:
DeleteQuerryDef('my querry')
Since this cannot be used in Access 2000 as it is written (I get
conversion
errors with this code), what is there in access 2000 that does the
same
thing? (If my assumptions on the use of this code is incorrect,
please
correct me as I am new to access coding).
My guess is that the previous programmer had written a custom function
that
he/she named DeleteQuerryDef, and that code within that function
deleted
the
query. You can delete a query by using
CurrentDb.QueryDefs("QueryName").Delete
The second problem I am having is kind of two parts:
1. Within this database, there is the use of a variable called
current_year
or curyear or something like that. This variable is an interger
that
holds
the current year (such as 2005). The problem with this coding
method
is
that
when the year changes to 2006 and beyond, someone will have to
recode
the
year variable. I want to be able to get the current year from a
function,
but I am not sure what will work, as I simply want to have this
variable
to
initialize to the current value of the year the program is run or
executed
or
whatever you want to call it. I have tried date() and I have though
about
now(), but I am not sure how to get the date or now function to work
into
displaying just the current year.
Use the Year function:
CurrentYearNumber = Year(Date())
What else I need to know is how does such a function work in a table.
If
I
have a column defined as a date and I have the date column setup to
display
the date as dd-mmm-yy and I want to convert the default output to just
a
date
(yyyy) only, what should I use as a default date and what format should
I
set
the date column as.
2. Once I have the current year, I need to be able to step through
a
looping function to either add data to a form or add data to a
table.
In
either case, I need to be able to step through a loop starting with
the
current year (say 2005 and work my way through the years to 1995 and
along
the way, I need to load a table such as ATS_LINES_2005,
ATS_LINES_2004,
etc.
In each case, the name of the file is the same except for the year.
Is
there
a way to load the file using a variable in the name (ie:
ATS_LINES_variable
for year such as ATS_LINES_2005 with the variable representing the
year
each
time it is executed). If so then I can get rid of the case
statements
as
they have to be re-created each year with new values for the year
search.
Load a table? name of file? You'll have to provide much more details
about
this. It's not clear what you're wanting to do here. Are you wanting
to
import data from some type of files?
The use of the file from above (ats_lines_2005, ats_lines_2004, etc.)
was
from a section of code that used a case statement from the user's input
in
a
form. The user would be asked for the information that he or she is
searching for (company name and name of project and what year to search
for).
As an example, the user could choose a company such as Shell and a
project
such as red rose (for instance). The year would be filled in through a
radio
button in the form (1995 to 2005 for choices). Once this information
is
entered, the program takes the results and uses the case statement to
load
the correct lines table into the program and does a search there. Thus
the
case statement compares the years from 95 to 2005, and each time it
loads
the
file from the match with the case statement. As of an example, if the
user
choose 2003 in the radio button, then the case statement 2003 would be
picked
and the file ATS_LINES_2003 would be loaded into the variables and a
search
would take place until a match is found. If no match is found then an
error
screen pops up telling the user that their project and company info
could
not
be found in the line table (ATS_LINES_2003). The user would then
search
again with a new year and the same company and project and a new date
from
the radio button choices.
My method of trying to change this situation is to use a do while
statement
that would begin in the year 2005 (or whatever the curren year is, from
what
you said could be done with the year(date()) functions and begin the
loop
at
that year and continue searching until a match is found, counting down
the
dates from 2005 until 1994. So the do while would be until 1994 is
reached
or a match is found. In using this method, I need to have a file
loaded
into
variables, one file at a time, using the name format (ATS_LINES_year).
And
in this case, it would start with ATS_LINES_2005 and would need to be
changed
for each file load into the variables for the serach (ATS_LINES_2005,
ATS_LINES_2004, ATS_LINES_2003, etc.). So what I needed to know
earlier
is
this: Can a variable to substituted in the name call such as
ATS_LINES_year_variable, where year_variable begins at 2005 and is
reduced
or
subtracted by 1 for each year in the database tables from 2005, 2004,
2003,
etc. until it hits 1994 which would break out of the loop as there is
no
1994
table.
The use of the case statement in this database is in a few places and I
would like to eliminate it as a programmer or simular person would have
to
go
back into the program each year and add to the case statements and the
current year variable as it is now.
Also, what other common problems might I see in converting code from
access
97 to access 2000 (I have downloaded and read the access 2000
conversion
white paper and did not get the specific answers I need to my
problems
so
please help me if you can).
Edward Letendre.
Edward Letendre.
