sql question

[Duane]

Hello,

I guess the easiest way to explain this problem is to show you what I am
confronted with. I have a project that was written in Paradox that I want
to do in Access. I am confronted with one major issue. I have been racking
my non-programmer brain trying to figure this out.

There is a table strictly used as a LOOKUP table, to determine which work
groups are off, on a given day. The table (Paradox) has 49 records.
Basically 7 different work schedules with 7 different variations for each
group. Some of the schedules are as follows;

Rotating Days Off
Set days off (Work 5, Off 2)
10-4 (Work 10, Off 4)
7-2-3-2 (Work 7,Off 2,Work 3,Off 2).
Each type of schedule has 7 different variations to the schedule.

The table (Paradox ROT.db) has the following fields; Group, GroupType,
Description. Then there are several fields labeled MOD concatenated with a
number. Field #1 starts out at MOD0 and the last field is MOD195, for a
total of 196 MOD fields. I assume there are 196 fields because it takes 196
days to make a complete cycle of all the schedules. The data type for all
the MOD fields is set to logical.

In the Paradox database, there is a textbox on the switchboard form that
gets the current date. Through code the date is converted to a long
integer. Then there is a MOD function to get the remainder, which is then
concatenated with a string "MOD" to get the end result of MOD162. Using
what I have tried to explain here, the current date is 04/20/2008. Then
long integer equivalent is 39558, and the MOD remainder is 162.

Here is the code that is used in the paradox database.

myDate = date(theDate) ;assigns date from main page to var
myLongDate = longInt(myDate) ;converts the date to a long integer

myMod = smallInt(myLongDate.mod(196)) ;gets the remainder of myLongDate
divided by 196

myField = "MOD"+string(myMod) ;creates a string = MOD + myMod converted to
string

;the following query finds the 14 RDO groups on myDate
myQ = Query

:WORK:RDOrot.db|Group |~myField|
|check |True |


The string variable of MOD162 is being used find out which of the 14 groups
are off on 04/20/2008, which are 4, 5, 13, 14, 18, 19, 20, 21, 36, 38, 39,
42, 44, 45.

On my form in Access, I have similar textboxes that return the same as the
Paradox version;

Dim MyDate As Date
Dim MyLongDate As Long
Dim MyResult As Integer
Dim MyMODResult as string

MyDate = Me.txtDate
MyLongDate = CLng(MyDate)
Me.txtLongDate = MyLongDate

MyResult = MyLongDate Mod 196
Me.txtResult = MyResult

MyMODResult = "MOD" & MyResult

I would like to emulate the same thing is Access, but I am not sure how I
can use the variable to query the table like what is being done in Paradox.
Can this be done in VBA or even the QBE in access? i.e., SELECT
MyTable.MyMODResult From MyTable WHERE MyTable.MyMODResult = -1;
(MyMODResult = a string variable of MOD162.)

The ultimate question is can I somehow use MyMODResult to query MyTable to
return the 14 groups that off work on 04/20/2008?

I appreciate any and all help.

Thanks in advance

 

[Duane]

Thanks for the response Bob.

I am not very good at building a querystring, but I will start reading up on
that. I would really like to figure this out. I have be looking at this,
leaving it, and then returning to for a few weeks later. This has been
going on for awhile.

You mentioned setting the value "MOD" + Forms!frm_Switchboard!txtResult in
the criteria row of the relevant column in the query. That's just it, "MOD"
+ txtResult IS the relevant column in the query.

In essence, I need to be able to query 1 of the 196 columns(fields) in the
table, however, the column is unknown until the Mod function returns the
MyResult, which in this example is 162. Once the 162 is concatenated with
the text "MOD", (MOD162), this is the actual Column (field) that I need to
query.

There are 49 records of which there are 14 records that are true any given
field. I need to know which records are true. Whenever the user changes
the date in the txtDate textbox, I want to be able to use the updated
txtResult value to know which field in the table I need to query.

On 04/20/2008, I need to query the field MOD162 to know which groups are off
work. On 04/21/2008, I would need to query field MOD163. On 04/22/2008 I
would need to query MOD164 and so on. I would have to create 196 different
queries.

Thanks again.