Re: sql question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

"Bob Quintal" <rquintal@xxxxxxxxxxxxx> wrote in message
news:Xns9A867EDD61B5ABQuintal@xxxxxxxxxxxxxxx
"Duane" <skydiver_2@xxxxxxxxx> wrote in
news:uffsw7voIHA.3960@xxxxxxxxxxxxxxxxxxxx:

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

yes, either by building a querystring and opening it in a recordset,
or also by setting the value "MOD" + Forms!<<formname>>!txtResult in
the criteria row of the relevant column in the query.





--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **


.

Relevant Pages

  • Re: sql question
    ... Paradox that I want to do in Access. ... Basically 7 different work schedules with 7 ... myLongDate = longInt;converts the date to a long integer ... ;the following query finds the 14 RDO groups on myDate ...
    (microsoft.public.access.formscoding)
  • Re: sql question
    ... IS the relevant column in the query. ... Dim strSQL as string ... Paradox that I want to do in Access. ... var myLongDate = longInt;converts the date to a long ...
    (microsoft.public.access.formscoding)
  • Re: Accessing a paradox table from a C# application...
    ... I would get a copy of Paradox and play around with restructuring the table and indexes and see what happens to query speed. ... There are several threads on the optimal way to set up a network. ... SELECT TOP 1000 * FROM ModelSer ...
    (comp.databases.paradox)
  • Re: Number of Records
    ... Paradox query process but seem to remember it can generate a lot of traffic ... > Diamond Software Group ... > Diamond Sports Gems ...
    (comp.databases.paradox)
  • Re: Accessing a paradox table from a C# application...
    ... SELECT TOP 1 * FROM ModelSer ORDER BY Timestamp ... How much faster is the query if you strip off the PX? ... The ODBC driver that I am using is the Microsoft Paradox ...
    (comp.databases.paradox)