how to use variables in queries

D

default105

I have the following code to put the date a student shows for class into the
proper table. I have included the entire code to hopefully better help you
understand what I am doing. The fields in this table are named for the year
respectively, ie... 2006, 2007, 2008, 2009 etc...

Is there a way to use a variable to allow me to tell the query to choose the
field based on the current year? I already have code to determine the
current year but I cannot figure out how to input it into replace the current
field [2006] in the DoCmd.RunSQL.

**************************CODE******************
************************************************
Private Sub Login_Click()
On Error GoTo Err_Login_Click

If Me.PassVerify = Me.Password Then 'determine correct password
Dim MemName, MyDate, MyMonth, YourMonth, MyDay, MyYear, TodayDate, AllowEntry

MyDate = Now() ' Assign a date.
MyMonth = month(MyDate) 'Determines current month number
YourMonth = MonthName(MyMonth) 'Determines current month name
MyDay = day(MyDate) 'Determines current day
MyYear = year(MyDate) 'Determines current year
MemName = Me.Username 'Show selected user from listbox in text box
TodayDate = MyMonth & "/" & MyDay & "/" & MyYear 'Set format for todays date
AllowEntry = DCount(MyYear, "Attendance", "[MemberID]=" & [MemberID])
'disable multiple logins
Me.Date.Value = TodayDate 'Place date in date text box for append query
If AllowEntry = 0 Then 'Determine if student has logged in
'Show Student they have logged in for class
Me.LoginOkLabel.Caption = MemName & " has attended class on " & YourMonth &
" " & MyDay & ", " & MyYear

'If the UserId field type is string, add a single quote before and after
DoCmd.RunSQL "INSERT INTO Attendance ( [MemberID], [2006]) VALUES (" &
[MemberID] & ", [Date])"
Else
'Alert Student they have already logged in
Dim EMsg, EStyle, ETitle, EHelp, ECtxt, EResponse
EMsg = "You have already logged in today," + Chr(13) + _
"Please click Ok" ' Define message.
EStyle = vbOKOnly + vbCritical ' Define buttons.
ETitle = "Already Logged In" ' Define title.
EHelp = "DEMO.HLP" ' Define Help file.
ECtxt = 1000 ' Define topic
' context.
' Display message.
EResponse = MsgBox(EMsg, EStyle, ETitle, EHelp, ECtxt)
End If

Else 'Notify student they entered the wrong password
Me.LoginOkLabel.Caption = "Incorrect Password, please try again"
Dim Msg, Style, Title, Help, Ctxt, Response
Msg = "The password you entered is incorrect," + Chr(13) + _
"Please try again" ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Login Failed" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End If


Me.PassVerify = Null 'Clear the password field

Exit_Login_Click:
Exit Sub

Err_Login_Click:
MsgBox Err.Description
Resume Exit_Login_Click

End Sub
 
A

Arvin Meyer [MVP]

First, you should have designed the system to use a single field for the
year. That way you could have used something like:

DoCmd.RunSQL "INSERT INTO Attendance ( [MemberID], [YearField]) VALUES (" &
[MemberID] & ", Year(Date()))"
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


default105 said:
I have the following code to put the date a student shows for class into the
proper table. I have included the entire code to hopefully better help you
understand what I am doing. The fields in this table are named for the year
respectively, ie... 2006, 2007, 2008, 2009 etc...

Is there a way to use a variable to allow me to tell the query to choose the
field based on the current year? I already have code to determine the
current year but I cannot figure out how to input it into replace the current
field [2006] in the DoCmd.RunSQL.

**************************CODE******************
************************************************
Private Sub Login_Click()
On Error GoTo Err_Login_Click

If Me.PassVerify = Me.Password Then 'determine correct password
Dim MemName, MyDate, MyMonth, YourMonth, MyDay, MyYear, TodayDate, AllowEntry

MyDate = Now() ' Assign a date.
MyMonth = month(MyDate) 'Determines current month number
YourMonth = MonthName(MyMonth) 'Determines current month name
MyDay = day(MyDate) 'Determines current day
MyYear = year(MyDate) 'Determines current year
MemName = Me.Username 'Show selected user from listbox in text box
TodayDate = MyMonth & "/" & MyDay & "/" & MyYear 'Set format for todays date
AllowEntry = DCount(MyYear, "Attendance", "[MemberID]=" & [MemberID])
'disable multiple logins
Me.Date.Value = TodayDate 'Place date in date text box for append query
If AllowEntry = 0 Then 'Determine if student has logged in
'Show Student they have logged in for class
Me.LoginOkLabel.Caption = MemName & " has attended class on " & YourMonth &
" " & MyDay & ", " & MyYear

'If the UserId field type is string, add a single quote before and after
DoCmd.RunSQL "INSERT INTO Attendance ( [MemberID], [2006]) VALUES (" &
[MemberID] & ", [Date])"
Else
'Alert Student they have already logged in
Dim EMsg, EStyle, ETitle, EHelp, ECtxt, EResponse
EMsg = "You have already logged in today," + Chr(13) + _
"Please click Ok" ' Define message.
EStyle = vbOKOnly + vbCritical ' Define buttons.
ETitle = "Already Logged In" ' Define title.
EHelp = "DEMO.HLP" ' Define Help file.
ECtxt = 1000 ' Define topic
' context.
' Display message.
EResponse = MsgBox(EMsg, EStyle, ETitle, EHelp, ECtxt)
End If

Else 'Notify student they entered the wrong password
Me.LoginOkLabel.Caption = "Incorrect Password, please try again"
Dim Msg, Style, Title, Help, Ctxt, Response
Msg = "The password you entered is incorrect," + Chr(13) + _
"Please try again" ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Login Failed" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End If


Me.PassVerify = Null 'Clear the password field

Exit_Login_Click:
Exit Sub

Err_Login_Click:
MsgBox Err.Description
Resume Exit_Login_Click

End Sub
Click to expand...
 
D

default105

Why would I want to only store the year in the year field. I am would like
to be able to go back through the years and see when the student has
attended. Plus I would show me how many student were at class before test
dates. I would rather be able to separate it by years with different fields
due to the fact that you can have an average of 40 to 100 students per month.
If I only used a year field I would have lets say for example 50 student
attend class 4 days a week for 3 years. 50*4*52*3 = 31200 entries in one
field to have to sort with code and queries for different years for form and
report applications. Can you use a variable in query to tell it the field to
put the value in based on the year since the field are named as the four
digit year

PS - I would like to thank Arvin Meyer for making available free downloads,
they have helped me greatly over the course of learning access programming as
I am sure it has many others, Thanks, Your work and knowledge is greatly
appreciated
 
R

Rick Brandt

default105 said:
Why would I want to only store the year in the year field. I am
would like to be able to go back through the years and see when the
student has attended. Plus I would show me how many student were at
class before test dates. I would rather be able to separate it by
years with different fields due to the fact that you can have an
average of 40 to 100 students per month. If I only used a year field
I would have lets say for example 50 student attend class 4 days a
week for 3 years. 50*4*52*3 = 31200 entries in one field to have to
sort with code and queries for different years for form and report
applications.
Click to expand...

Your current setup is a good design for a spreadsheet. It is not a good design
for a database and the design is the reason for your current dilemma.
Can you use a variable in query to tell it the field
to put the value in based on the year since the field are named as
the four digit year
Click to expand...

In VBA code you could re-write the SQL for the query using a variable to choose
which year field to include in the SELECT clause. Otherwise it would take a
custom function that would return the value of the variable and then a complex
IIf() or Switch() expression that would return the desired year based on the
value returned from the function.
 
D

default105

Based on what you are saying, would you recommend having it store this
information externally in a excel speadsheet then. Your input is appreciated
 
A

Albert D.Kallal

default105 said:
Based on what you are saying, would you recommend having it store this
information externally in a excel speadsheet then. Your input is
appreciated
Click to expand...

No, what the comments state is that you are trying to manage company
information using a spreadsheet approach, and not one of a database
approach. At a certain point, this is going to be considered incompetence,
or a waste of developer and company time. Thus at a certain point, this
means you could loose your job over incompetence, or simply be accused of
wasting company time that could be otherwise be used to feed the poor, or
used for other projects). Some companies accept the abuse of company
vehicles and wastage, and some don't tolerate this much.

Hence, the poster is simply suggesting that is not the way information
(database) systems work. If anyone with IT training looks at what you are
doing, they will instantly see that things are being done wrong and thus you
are wasting valuable labor and time that could better used else where. So,
to be clear, the problem you are facing is un-normalized data.

While the above comments are kind of tongue in cheek and I mean no harm to
you, they do ring some words of wisdom.
If I only used a year field I would have lets say for example 50 student
Click to expand...
attend class 4 days a week for 3 years. 50*4*52*3 = 31200 entries in one
field to have to sort with code and queries for different years for form and
report applications.

Well, 31,000 records is a VERY small table here. Sure, you have to sort with
code, but at least at the start of a new year, you don't have to modify the
data input forms. Nor do you have to modify the reports and existing code if
you normalize this data. What happens when you write some fancy processing
routines. Or what about building nice reports, or even student information
reports? (they will all have to be modified to display different years).
Worse, is that summary information is even hard to build with what you have.
What happens when you want attendance from November to January? Now, you
have
data that bridges two years, and you can't build quires that work access two
fields like you have.

Worse, what happens when you leave, and someone else has to take over this
database? You mean now at the start of a new year, they have to modify
reports, modify forms, modify code? Modify tables?

I don't think anyone would take too kind
to using a system that requites MODIFYING the APPLICATION at the start of a
new year - boy, I wish my clients were like that..but then again, they would
run me out of town for being a crook and trying to extort money out of them
every year. Remember, the EXPENSIVE part is the human developer cost to
maintain this system, and a file with 10,000, or 150,000 records is really
nothing for ms-access to report on.

Think of it this way: If you needed to modify your accounting package every
new year to add fields to the code, reports, and screens, you would spend
MORE TIME modifying the appcation then actually doing any productive work.
And, it seems now you starting to see this point in your application (you
are
now looking for code to modify the MANY quires etc. that are HARD coded to a
particular year. Not to mention the many reports, code, and query you
already
built. You can see this starting to spiral out of control quite fast here.

You most certainly can insert the field you want in code, but then what
about
sending that query to a report? You now need to somehow modify the report to
also reflect this different field..and the whole processing becomes a dog
chasing its tail. And, as you build more and more code, forms, reports, the
problem you have will become worse and worse until eventually everyone
thinks
your system is no good, and then they bring in a bunch of high priced oracle
guys....

Again, I SINCERELY apologize if any of the above seems rude in anyway. I am
being tongue in cheek, however, I do think that the above is a very
legitimate attempt to explain WHY you receiving so many posts that tell you
that you are doing things the wrong way. It is just clear as day to anyone
who has worked with this stuff that what you have is not good.

However, you can't build, or change Rome in a day. So, after trying to
explain why so many are telling you to avoid the path you are on, you can
most certainly use a variable, and here is how you do it:

The following would work

dim strYearField as string

strYearField = "2006"

DoCmd.RunSQL "INSERT INTO Attendance ( [MemberID], [" & strYearField & "]
VALUES
(" & [MemberID] & ", [Date])"


Again, please take the comments as tongue in cheek, but do be aware that
some
IT people in your organization may not have near as kind words as I did
above....

And, good luck...I firmly believe that you have to work with what you
have..so, make the goal of normalizing your data something that you consider
for the future long term success of this project...
 
A

Albert D.Kallal

Sure, you have to sort with
code

Should read,

Sure, you have to sort with with a particular year
 
Top