## Queries

Expand Messages
• Hi! I m a raw beginner here, in a remote area in Alaska. Can t get to any training and the one book I have for Access 2002 is useless in helping me do
Message 1 of 15 , Aug 29, 2003
Hi!

I'm a raw beginner here, in a remote area in Alaska. Can't
get to any training and the one book I have for Access 2002 is
useless in helping me do calculations. We don't have bookstores up
here that we can browse through the books to see which one would be
the right one.

I'm trying to modify an existing database and add a few
extras. What I have is a simple form that has Date and Visitation
numbers. The number I input is from 'people counters' counting
people who come in and out of our visitor center. Because people
come and go, we divide the number in half to get our visitation
number. I'm trying to find out how to make a calculation where
Access will divide the number I input in half. Everytime I try to
input a calculation, I get several error messages. The book just
keeps confusing me more and some resources on the internet are not
simplistic enough for me as they get into complex calculations, I
just need a simple division. Please be patient with me and explain
this in very simplistic terms. I'm not a programmer, I'm not a
business person and feel like an idiot for all the hours I have put
into trying to understand ACCESS.

Christy
• Even in Alaska you have the Help File for Access. I don t think they strip it out for copies of Access sold only to Alaska residents :) Under Help, lookup
Message 2 of 15 , Aug 29, 2003
Even in Alaska you have the Help File for Access. I don't think they strip
it out for copies of Access sold only to Alaska residents :) Under Help,
lookup the Eval function. It would be something along the lines of

MyCalc = Eval(N/2)

N representing the number you enter.

HTH,

Beth M
PS We were all new once :)

-----Original Message-----
From: care4k9ss [mailto:care4k9@...]
Sent: Friday, August 29, 2003 1:02 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Queries

Hi!

I'm a raw beginner here, in a remote area in Alaska. Can't
get to any training and the one book I have for Access 2002 is
useless in helping me do calculations. We don't have bookstores up
here that we can browse through the books to see which one would be
the right one.

I'm trying to modify an existing database and add a few
extras. What I have is a simple form that has Date and Visitation
numbers. The number I input is from 'people counters' counting
people who come in and out of our visitor center. Because people
come and go, we divide the number in half to get our visitation
number. I'm trying to find out how to make a calculation where
Access will divide the number I input in half. Everytime I try to
input a calculation, I get several error messages. The book just
keeps confusing me more and some resources on the internet are not
simplistic enough for me as they get into complex calculations, I
just need a simple division. Please be patient with me and explain
this in very simplistic terms. I'm not a programmer, I'm not a
business person and feel like an idiot for all the hours I have put
into trying to understand ACCESS.

Christy
• Alaska?! Am I typing hard enough for you to read this? First, I suggest you name Date something like VisitDate because Date is a reserved word and could cause
Message 3 of 15 , Aug 29, 2003

First, I suggest you name Date something like VisitDate because Date is a
reserved word and could cause you trouble.

Here's a fairly simple approach. Open your form in design view and make sure
the control wizard is turned off. That's the wand icon right by the fat
arrow in the Toolbox toolbar (the one with all the controls like text boxes,
labels, etc).

add a text box next to your count text box. Let's call the new text box
txtRawCount. This is where you will enter the number you want halved. I'm
going to assume the text box that holds you halved count is called
Visitation.

If the Properties box isn't open, click the icon in the menu to open it.
Then highlight txtRawCount. It should have a bunch of little squares around
it.

Click on the Events tab of the properties box. Find the On Lost Focus event.
Type in that line an open square bracket [

The line should autofill with [EventProcedure]. You'll see a builder button
to the right of the line. It looks like 3 dots (...). Click it. That will
open the code window to your LostFocus event for txtRawCount. You'll see
this code:

Private Sub txtRawCount_LostFocus()

End Sub

Put code between the 2 lines so it looks like this:

Private Sub txtRawCount_LostFocus()
Dim lngRawCount As Long

'Take care of odd numbers...Someone came in but
'didn't leave through expected exit.
If (Me.txtRawCount Mod 2) = 1 Then
lngRawCount = Me.txtRawCount + 1
Else
lngRawCount = Me.txtRawCount
End If

Me.Vistitation = lngRawCount / 2

End Sub

Now when you enter the raw count and hit Enter or Tab, the Visitation number
will be calculated for you to be half of the raw number. I tossed in a check
for odd numbers assuming that someone may have left through an exit that
doesn't count traffic.

Hey, guess what? you're now a programmer!
Regards,
Bill Mosca
Founder

-----Original Message-----
From: care4k9ss [mailto:care4k9@...]
Sent: Friday, August 29, 2003 11:02 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Queries

Hi!

I'm a raw beginner here, in a remote area in Alaska. Can't
get to any training and the one book I have for Access 2002 is
useless in helping me do calculations. We don't have bookstores up
here that we can browse through the books to see which one would be
the right one.

I'm trying to modify an existing database and add a few
extras. What I have is a simple form that has Date and Visitation
numbers. The number I input is from 'people counters' counting
people who come in and out of our visitor center. Because people
come and go, we divide the number in half to get our visitation
number. I'm trying to find out how to make a calculation where
Access will divide the number I input in half. Everytime I try to
input a calculation, I get several error messages. The book just
keeps confusing me more and some resources on the internet are not
simplistic enough for me as they get into complex calculations, I
just need a simple division. Please be patient with me and explain
this in very simplistic terms. I'm not a programmer, I'm not a
business person and feel like an idiot for all the hours I have put
into trying to understand ACCESS.

Christy

To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

[Non-text portions of this message have been removed]
• Beth and Bill, Yeah, it is amazing that we do have computers up here. When my seasonal job is over, I m returning back home in California. Thank you for your
Message 4 of 15 , Aug 29, 2003
Beth and Bill,

Yeah, it is amazing that we do have computers up here. When my
seasonal job is over, I'm returning back home in California.

Thank you for your time and patience. I have been using
the 'Help' that comes with Access 2002, looking under calculations
etc. and just couldn't figure it out.

Bill writes...
> First, I suggest you name Date something like VisitDate because
Date is a
> reserved word and could cause you trouble.

Thank you! Done!

> Here's a fairly simple approach...snip...

Great step-by-step procedure. Was able to follow really easy!

Got the Event Procedure in....except now in my fields it shows
#Name ? and all the data that was entered is gone. No biggie, I can
re-enter it since I haven't put all the data in if this would
happen.

> Hey, guess what? you're now a programmer!

Thanks for the encouragement, but its not running smooth
yet ;-)

Christy
• I guess I should have told you my method would work for Single form view only. I forget that probably because that s how I usually design forms. You lose all
Message 5 of 15 , Aug 29, 2003
I guess I should have told you my method would work for Single form view
only. I forget that probably because that's how I usually design forms.

You lose all the data in the other rows if you have a continuous or
datasheet form. You also lose most of your control using these styles of
forms.

As far as the #Name error, check to see that the code reflects the names of
the fields and controls.

If you'd care to post your code that is in the LostFocus event, we might be
able to spot something.

And what makes you think that programmers write bug-free code? we just write
code and hope for the best. :O)
Regards,
Bill Mosca
Founder

-----Original Message-----
From: care4k9ss [mailto:care4k9@...]
Sent: Friday, August 29, 2003 1:41 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Queries

Beth and Bill,

Yeah, it is amazing that we do have computers up here. When my
seasonal job is over, I'm returning back home in California.

Thank you for your time and patience. I have been using
the 'Help' that comes with Access 2002, looking under calculations
etc. and just couldn't figure it out.

Bill writes...
> First, I suggest you name Date something like VisitDate because
Date is a
> reserved word and could cause you trouble.

Thank you! Done!

> Here's a fairly simple approach...snip...

Great step-by-step procedure. Was able to follow really easy!

Got the Event Procedure in....except now in my fields it shows
#Name ? and all the data that was entered is gone. No biggie, I can
re-enter it since I haven't put all the data in if this would
happen.

> Hey, guess what? you're now a programmer!

Thanks for the encouragement, but its not running smooth
yet ;-)

Christy

To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

[Non-text portions of this message have been removed]
• Hi Bill, ... form view ... forms. Hmmmm, yeah, I m going by what the person who was here last year programmed. She set it up as a continuous form for other
Message 6 of 15 , Aug 29, 2003
Hi Bill,

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca"
<wrmosca@c...> wrote:
> I guess I should have told you my method would work for Single
form view
> only. I forget that probably because that's how I usually design
forms.

Hmmmm, yeah, I'm going by what the person who was here last
year programmed. She set it up as a continuous form for other places
that we are collecting visitation. We now put new counters in this
one museum and I'm trying to develop a new form for it.

> As far as the #Name error, check to see that the code reflects the
names of
> the fields and controls.

> If you'd care to post your code that is in the LostFocus event, we
might be
> able to spot something.

I pretty much cut and pasted what you put with slight
modification to match spelling.

Private Sub TxtRawCount_LostFocus()
Dim lngRawCount As Long

'Take care of odd numbers...Someone came in but
'didn't leave through expected exit.
If (Me.TxtRawCount Mod 2) = 1 Then
lngRawCount = Me.TxtRawCount + 1
Else
lngRawCount = Me.TxtRawCount
End If

Me.Visitation = lngRawCount / 2

End Sub

> And what makes you think that programmers write bug-free code? we
just write
> code and hope for the best. :O)

Yeah, I'm sure hoping! I honestly have been tackling this for a
few months now between my major duties. I took one of the hardest
Collateral Duties here! Statistics! HA!

Christy
• hi, Can you please clear my following doubts: 1. How to create and add your own icon to the MDE file ? 2. How to disable all the access menus. I just want my
Message 7 of 15 , Jul 30, 2005
hi,

Can you please clear my following doubts:
1. How to create and add your own icon to the MDE file ?
2. How to disable all the access menus. I just want my application to run and dont want to display any menu. When I tried I get few options like File etc etc.
3. How to disable the shift action during startup.

Regards
Suresh

------------------------------------------------------------
K. Suresh
email: k.suresh@...
Dir: 23352273, 23352527
------------------------------------------------------------

Life can be a rocky road. The Challenge is not to let it grind
you into dust, but to polish you into a brilliant gem - J.M.Fogg

---NOTICE---
The contents and / or attachments of this email are subjected to the DISCLAIMER
AND PRIVILEGE NOTICE displayed at http://timesmail.timesgroup.com/disclaimer.html

[Non-text portions of this message have been removed]
• Building a database of Contacts. In this DB I have a table for the contact and a related table for employees (if there are any). I have also created a temp
Message 8 of 15 , Oct 5, 2007
Building a database of Contacts. In this DB I have a table for the
contact and a related table for employees (if there are any). I have
also created a temp table to collect either the contact's names or
employee's names or both. This temp table is to be used in search
parameters to find a specifique person and to which record He or She
belongs. This table is loaded using an action query (append) and
emptied with a delete query.

Now my problem. The action query, called "qryListEmployees" loads the
contact number and employee names without any problem but I want to
add a "label" through this query which will identify the name as an
employee. The temp table has a field called ContactEmployee to
receive this label (or text) but I am at a lose to include the
instruction in the QBE Grid.

Can anyone help

John
• John- In a blank field in your Append query, enter: ContactEmployee: Employee .. and set the Append To to ContactEmployee. John Viescas, author Microsoft
Message 9 of 15 , Oct 5, 2007
John-

In a blank field in your Append query, enter:

ContactEmployee: "Employee"

.. and set the Append To to ContactEmployee.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of johnlond1
Sent: Friday, October 05, 2007 5:34 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Queries

Building a database of Contacts. In this DB I have a table for the
contact and a related table for employees (if there are any). I have
also created a temp table to collect either the contact's names or
employee's names or both. This temp table is to be used in search
parameters to find a specifique person and to which record He or She
belongs. This table is loaded using an action query (append) and
emptied with a delete query.

Now my problem. The action query, called "qryListEmployees" loads the
contact number and employee names without any problem but I want to
add a "label" through this query which will identify the name as an
employee. The temp table has a field called ContactEmployee to
receive this label (or text) but I am at a lose to include the
instruction in the QBE Grid.

Can anyone help

John

• It works! thank you. I don t understand why. ContactEmployee is a field in the temporary table, why should it be entered into the line reserved for the
Message 10 of 15 , Oct 5, 2007
It works! thank you. I don't understand why. ContactEmployee is a
field in the temporary table, why should it be entered into the line
reserved for the source table?

John

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas"
<JohnV@...> wrote:
>
> John-
>
> In a blank field in your Append query, enter:
>
> ContactEmployee: "Employee"
>
> .. and set the Append To to ContactEmployee.
>
> John Viescas, author
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
> For the inside scoop on Access 2007, see:
> http://blogs.msdn.com/access/
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of johnlond1
> Sent: Friday, October 05, 2007 5:34 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Queries
>
> Building a database of Contacts. In this DB I have a table for the
> contact and a related table for employees (if there are any). I have
> also created a temp table to collect either the contact's names or
> employee's names or both. This temp table is to be used in search
> parameters to find a specifique person and to which record He or She
> belongs. This table is loaded using an action query (append) and
> emptied with a delete query.
>
> Now my problem. The action query, called "qryListEmployees" loads the
> contact number and employee names without any problem but I want to
> add a "label" through this query which will identify the name as an
> employee. The temp table has a field called ContactEmployee to
> receive this label (or text) but I am at a lose to include the
> instruction in the QBE Grid.
>
> Can anyone help
>
> John
>
>
>
>
>
• John- You re simply providing a literal constant to be inserted into that column. If you switch to SQL View, it should look something like: INSERT INTO
Message 11 of 15 , Oct 5, 2007
John-

You're simply providing a literal constant to be inserted into that column.
If you switch to SQL View, it should look something like:

INSERT INTO TEMPTable (ContactNo, ContactName, ContactEmployee)
SELECT ContactNo, EmpName, "Employee" As ContactEmployee
FROM Employees
WHERE ...

Or open your Append query in Datasheet view by opening it in Design view and
then choosing Datasheet on the toolbar. You'll see the constant value
"Employee" produced in every row.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of johnlond1
Sent: Saturday, October 06, 2007 3:25 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Queries

It works! thank you. I don't understand why. ContactEmployee is a
field in the temporary table, why should it be entered into the line
reserved for the source table?

John

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas"
<JohnV@...> wrote:
>
> John-
>
> In a blank field in your Append query, enter:
>
> ContactEmployee: "Employee"
>
> .. and set the Append To to ContactEmployee.
>
> John Viescas, author
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
> For the inside scoop on Access 2007, see:
> http://blogs.msdn.com/access/
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of johnlond1
> Sent: Friday, October 05, 2007 5:34 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Queries
>
> Building a database of Contacts. In this DB I have a table for the
> contact and a related table for employees (if there are any). I have
> also created a temp table to collect either the contact's names or
> employee's names or both. This temp table is to be used in search
> parameters to find a specifique person and to which record He or She
> belongs. This table is loaded using an action query (append) and
> emptied with a delete query.
>
> Now my problem. The action query, called "qryListEmployees" loads the
> contact number and employee names without any problem but I want to
> add a "label" through this query which will identify the name as an
> employee. The temp table has a field called ContactEmployee to
> receive this label (or text) but I am at a lose to include the
> instruction in the QBE Grid.
>
> Can anyone help
>
> John
>
>
>
>