Help with storing variable name in a table 
Author Message
 Help with storing variable name in a table

PLEASE HELP ME?

Here is what I am trying to do (in VB):

1) I store some various dates into Public fields
(Beginning of the Month = 3/1/02) - Dim begofmonth as
Date, Dim y as Date, etc.
2) I run a popup box that asks the user to select a date
(begofmonth or y or z)
3) Based on the selection I want to look up the public
field in a table and set the date = to the public field
name stored in the table.

The problem I have is that the name I pull from the table
brings back the string instead of the actual field name.  
Any ideas?  Below is the code.

Function SetDefaultBegEndDates(DateRangeNumber)

    Dim con As Object
    Dim rs As Object
    Dim strSql As String
    Dim intOption As Integer

    ' Open the table of Date Ranges
    Set con = Application.CurrentProject.Connection
    strSql = "SELECT * FROM [Date Range Default] WHERE
DateRangeID =" & DateRangeNumber & ";"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSql, con, 1   ' 1 = adOpenKeyset

    ' Set Beginning and Ending Dates
    rs.MoveFirst
    While (Not (rs.EOF))
        If rs![DateRangeID] = DateRangeNumber Then
            [BeginningDateDefault] = rs!BegDateField
            [EndingDateDefault] = rs!EndDateField
            rs.MoveLast
        End If
        rs.MoveNext
    Wend

    ' Close the recordset and the database.
    rs.Close
    Set rs = Nothing
    Set con = Nothing

The code above is meant to replace this {*filter*} if statement
and to allow me to add more types.    

    If DateRangeNumber = 1 Then
        [BeginningDateDefault] = AllDatesBeginningDate  <--
----- these are calculated in a different function
        [EndingDateDefault] = AllDatesEndingDate <-------
these are calculated in a different function
    ElseIf DateRangeNumber = 2 Then
        [BeginningDateDefault] = Last12Months
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 3 Then
        [BeginningDateDefault] = Last30days
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 4 Then
        [BeginningDateDefault] = Last7days
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 5 Then
        [BeginningDateDefault] = CurrentMonthFirstDay
        [EndingDateDefault] = CurrentMonthLastDay
    ElseIf DateRangeNumber = 6 Then
        [BeginningDateDefault] = LastMonthFirstDay
        [EndingDateDefault] = LastMonthLastDay
    ElseIf DateRangeNumber = 7 Then
        [BeginningDateDefault] = NextMonthFirstDay
        [EndingDateDefault] = NextMonthLastDay
    ElseIf DateRangeNumber = 8 Then
        [BeginningDateDefault] = CurrentMonthFirstDay
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 9 Then
        [BeginningDateDefault] = Date
        [EndingDateDefault] = Next12Months
    ElseIf DateRangeNumber = 10 Then
        [BeginningDateDefault] = Date
        [EndingDateDefault] = Next30days
    ElseIf DateRangeNumber = 11 Then
        [BeginningDateDefault] = Date
        [EndingDateDefault] = Next7days
    ElseIf DateRangeNumber = 12 Then
        [BeginningDateDefault] = CurrentWeekBeginningDate
        [EndingDateDefault] = CurrentWeekEndingDate
    ElseIf DateRangeNumber = 13 Then
        [BeginningDateDefault] = LastWeekBeginningDate
        [EndingDateDefault] = LastWeekEndingDate
    ElseIf DateRangeNumber = 14 Then
        [BeginningDateDefault] = NextWeekBeginningDate
        [EndingDateDefault] = NextWeekEndingDate
    ElseIf DateRangeNumber = 15 Then
        [BeginningDateDefault] = CurrentWeekBeginningDate
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 16 Then
        [BeginningDateDefault] = CurrentYearJan1
        [EndingDateDefault] = CurrentYearDec31
    ElseIf DateRangeNumber = 17 Then
        [BeginningDateDefault] = LastYearJan1
        [EndingDateDefault] = LastYearDec31
    ElseIf DateRangeNumber = 18 Then
        [BeginningDateDefault] = NextYearJan1
        [EndingDateDefault] = NextYearDec31
    ElseIf DateRangeNumber = 19 Then
        [BeginningDateDefault] = CurrentYearJan1
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 21 Then
        [BeginningDateDefault] = Date
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 22 Then
        [BeginningDateDefault] = Date
        [EndingDateDefault] = Date + 1
    ElseIf DateRangeNumber = 23 Then
        [BeginningDateDefault] = DateAdd("m", -6, Date) + 1
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 24 Then
        [BeginningDateDefault] = DateAdd("m", -3, Date) + 1
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 25 Then
        [BeginningDateDefault] = Date
        [EndingDateDefault] = DateAdd("m", 6, Date) - 1
    ElseIf DateRangeNumber = 26 Then
        [BeginningDateDefault] = Date
        [EndingDateDefault] = DateAdd("m", 3, Date) - 1
    ElseIf DateRangeNumber = 27 Then
        [BeginningDateDefault] = Date - 1
        [EndingDateDefault] = Date
    ElseIf DateRangeNumber = 28 Then
        [BeginningDateDefault] = Date - 1
        [EndingDateDefault] = Date + 1
    End If

End Function



Sat, 04 Sep 2004 05:03:17 GMT  
 Help with storing variable name in a table
Dave

I may not fully understand (no, make that, I barely have a clue about) what
you are trying to do, but it "tastes like" you are storing begin and end
terms (strings) that you wish to use to constrain some other calculation or
query.

I've done something similar, though not on the scale of your effort.  I used
built-in Access DateTime functions as strings in a table to calculate a
"default" expiration date for 10 or so rows.  I entered the actual string
... e.g., "DateSerial(Year(Date()), Month(Date)+1, 0)", in preparation to
return the last day of the current month.  To actually get the value, not
the string, I used:
    Eval(Me!MyComboBox.Column(x))
where MyComboBox was the combo box the user used to select one of the items,
and Column(x) was the (n-1) field in the rowsource, pointed at the table and
field that held the expression.  This returned the evaluated value of the
expression in the field.

Hope I had a clue!

Good luck

Jeff Boyce
<Access MVP>



Sat, 04 Sep 2004 19:47:35 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Referencing a variable name stored in a variable

2. Help!! Need variable name from other variables

3. Help!! Need variable name from other variables

4. Variables stored in Range Names

5. Store the active worksheet(name) into variable

6. Setting a control who's name is stored in a variable

7. accessing control name stored in a variable

8. Storing subform name in variable when clicked

9. Reffering to Control when Name is stored in Variable

10. Temp QueryDef or Stored QueryDef Variable Name


 
Powered by phpBB® Forum Software © phpBB Group