Populate a field by query

[Matt]

Howdy all,

I have an interesting project going and need some help. I have created a
form for an amateur radio operator to use as a log for a net (if you want
more info on this contact me off list).

I have txt boxes for call, name, town, county, and list. 2 checkboxes for
“mobile†and "Check in only". At the top are the time EST, time UTC, and
week number. These are automatic.

What I am trying to do is set up a query to populate the "List" box with all
the people that checked in for the current week.

So for week #1, it will show the check ins in a list like:
kc8bew
kc8tds
wb8trk
ka8pcp
n2knc
etc...

Only the current week is seen. The kicker is to sort OUT the "check in only"
so they are not called again. Also, if possible, a station with the "mobile"
checked is shown differently either a "/m" beside their call or the text
different color for example.

This probably runs into several queries. I'll do what it takes to get the
result.

Thanks for the help,
Matt, kc8bew

 

[John W. Vinson]

I have txt boxes for call, name, town, county, and list. 2 checkboxes for
“mobile” and "Check in only". At the top are the time EST, time UTC, and
week number. These are automatic.

What I am trying to do is set up a query to populate the "List" box with all
the people that checked in for the current week.

So for week #1, it will show the check ins in a list like:
kc8bew
kc8tds
wb8trk
ka8pcp
n2knc
etc...

Only the current week is seen. The kicker is to sort OUT the "check in only"
so they are not called again. Also, if possible, a station with the "mobile"
checked is shown differently either a "/m" beside their call or the text
different color for example.

This probably runs into several queries. I'll do what it takes to get the
result.

One simple query should do it. What's the structure of your Table (the form is
irrelevant to this problem)?

You would just need a calculated field

ShowCall: [Call] & IIF([Mobile], "/m", "")

to display the call letters; a criterion of False on the [Check in only]
yes/no field; and a criterion of

DateAdd("d", -7, Date())

on the date/time of the call.

Set the query's Unique Values properties to True and sort by ShowCall to see
each call sign only once.

I suspect that Tony Toews (Microsoft Access MVP and very experienced ham radio
operator) could give you better advice but this should get you started.

John W. Vinson [MVP]

 

[Matt]

One simple query should do it. What's the structure of your Table (the form is
irrelevant to this problem)?

net number, call, name, town, county, time, utc, list, mobile, check only

You would just need a calculated field

ShowCall: [Call] & IIF([Mobile], "/m", "")

Is this right? In the query’s design view, ShowCall: [Call] goes under
“call†field and IIF([Mobile], "/m", "") under the “mobile†field? Then,
DateAdd("d", -7, Date()) under the “net number†field? Somehow this is how
it shows up. I don’t honestly know how I got this.

to display the call letters; a criterion of False on the [Check in only]
yes/no field; and a criterion of

DateAdd("d", -7, Date())

on the date/time of the call.

Is this subtracting 7 days from current date? If so I am only want to show
the same days calls. This will be just the people checking in on that day.

Set the query's Unique Values properties to True and sort by ShowCall to see
each call sign only once.

“Unique Values properties to True†is that the same as “yesâ€. Using Access
2003.

I suspect that Tony Toews (Microsoft Access MVP and very experienced ham radio
operator) could give you better advice but this should get you started.
COOL!

John W. Vinson [MVP]

Thanks for the help John!

Matt

 

[John W. Vinson]

One simple query should do it. What's the structure of your Table (the form is
irrelevant to this problem)?

net number, call, name, town, county, time, utc, list, mobile, check only

You would just need a calculated field

ShowCall: [Call] & IIF([Mobile], "/m", "")

Is this right? In the query’s design view, ShowCall: [Call] goes under
“call” field and IIF([Mobile], "/m", "") under the “mobile” field? Then,
DateAdd("d", -7, Date()) under the “net number” field? Somehow this is how
it shows up. I don’t honestly know how I got this.

Not what I intended!

Instead, in the query design grid find a vacant cell in the Field row. Copy
and paste, or type, the expression into the field cell. This will create a new
field in the query containing the text that you want to see.

to display the call letters; a criterion of False on the [Check in only]
yes/no field; and a criterion of

DateAdd("d", -7, Date())

on the date/time of the call.

Is this subtracting 7 days from current date? If so I am only want to show
the same days calls. This will be just the people checking in on that day.

Not what you said, or perhaps I misunderstood: in your original post you had

What I am trying to do is set up a query to populate the "List" box with all
the people that checked in for the current week.

The expression > DateAdd("d", -7, Date()) will return all records where the
date field falls within the past week. You can of course use whatever
criterion you want; =Date() will return records where the date field is equal
to today's date, UNLESS you are including the time with the date, in which
case you need a criterion of either

= Date()

or

BETWEEN Date() AND Date() + 1

to get all timepoints during today.

“Unique Values properties to True” is that the same as “yes”. Using Access
2003.

COOL!

Check out his website if you're interested:
http://www.granite.ab.ca/access

John W. Vinson [MVP]

 

[Matt]

You would just need a calculated field

ShowCall: [Call] & IIF([Mobile], "/m", "")

Is this right? In the query’s design view, ShowCall: [Call] goes under
“call†field and IIF([Mobile], "/m", "") under the “mobile†field? Then,
DateAdd("d", -7, Date()) under the “net number†field? Somehow this is how
it shows up. I don’t honestly know how I got this.

Not what I intended!

Instead, in the query design grid find a vacant cell in the Field row. Copy
and paste, or type, the expression into the field cell. This will create a new
field in the query containing the text that you want to see.

Got it working now! Just deleted the other query and started over.

to display the call letters; a criterion of False on the [Check in only]
yes/no field; and a criterion of

DateAdd("d", -7, Date())
on the date/time of the call.

Is this subtracting 7 days from current date? If so I am only want to show
the same days calls. This will be just the people checking in on that day.

Not what you said, or perhaps I misunderstood: in your original post you had

Sorry for the mix-up.

The expression > DateAdd("d", -7, Date()) will return all records where the
date field falls within the past week. You can of course use whatever
criterion you want; =Date() will return records where the date field is equal
to today's date, UNLESS you are including the time with the date, in which
case you need a criterion of either

Done and it works good. Used >=Date()

I am seeing all the calls for the day. Now I need to sort them by time.
They are alphabetical at the moment.

Check out his website if you're interested:
http://www.granite.ab.ca/access

I will do that.

Thanks again,
Matt

 

[John W. Vinson]

Done and it works good. Used >=Date()

I am seeing all the calls for the day. Now I need to sort them by time.
They are alphabetical at the moment.

Just put Ascending on the date/time field's Sort row in the query grid.

John W. Vinson [MVP]

 

[Matt]

Just put Ascending on the date/time field's Sort row in the query grid.

Already done!

One last thing regarding this box if I may...... Is there a way to not show
duplicate calls?

Thanks!
matt

 

[John W. Vinson]

Already done!

One last thing regarding this box if I may...... Is there a way to not show
duplicate calls?

Set the Unique Values property of the query to Yes. Perhaps you could post the
SQL view of the query, indicate what it's showing, and what you want it to
show.

John W. Vinson [MVP]

 

[Matt]

Set the Unique Values property of the query to Yes. Perhaps you could post the
SQL view of the query, indicate what it's showing, and what you want it to
show.

sql view:

SELECT DISTINCT [Call] & IIf([Mobile],"/m","") & IIf([check]," - check","")
AS ShowCall
FROM net
WHERE ((([Call] & IIf([Mobile],"/m","") & IIf([check]," -
check",""))>=Date()))
ORDER BY net.time DESC;

It was working now I am getting an error #3093. As you saw I posted last
night at 3am. Wasn't really clear headed <grin>. I am showing calls from a
couple days ago when I started this. Maybe I need to step back and look at
it from the beginning.

thanks,
matt

 

[John W. Vinson]

Set the Unique Values property of the query to Yes. Perhaps you could post the
SQL view of the query, indicate what it's showing, and what you want it to
show.

sql view:

SELECT DISTINCT [Call] & IIf([Mobile],"/m","") & IIf([check]," - check","")
AS ShowCall
FROM net
WHERE ((([Call] & IIf([Mobile],"/m","") & IIf([check]," -
check",""))>=Date()))
ORDER BY net.time DESC;

It was working now I am getting an error #3093. As you saw I posted last
night at 3am. Wasn't really clear headed <grin>. I am showing calls from a
couple days ago when I started this. Maybe I need to step back and look at
it from the beginning.

You're comparing the call letters to today's date... apples to bicycles, not
just apples to oranges!

The WHERE clause should be something like

WHERE net.[time] >= Date()

and should not refer to the Call calculated field at all.

If you're using it for a combo box, do you really want the rows sorted by the
time that the call came in? Maybe you need to sort by the Call field instead.

John W. Vinson [MVP]

 

[Matt]

sql view:

SELECT DISTINCT [Call] & IIf([Mobile],"/m","") & IIf([check]," - check","")
AS ShowCall
FROM net
WHERE ((([Call] & IIf([Mobile],"/m","") & IIf([check]," -
check",""))>=Date()))
ORDER BY net.time DESC;

It was working now I am getting an error #3093. As you saw I posted last
night at 3am. Wasn't really clear headed <grin>. I am showing calls from a
couple days ago when I started this. Maybe I need to step back and look at
it from the beginning.

You're comparing the call letters to today's date... apples to bicycles, not
just apples to oranges!

The WHERE clause should be something like

WHERE net.[time] >= Date()

and should not refer to the Call calculated field at all.

If you're using it for a combo box, do you really want the rows sorted by the
time that the call came in? Maybe you need to sort by the Call field instead.

Tony would be able to give a longer explanation of a net but the order the
stations come in needs to be shown. The stations will be called again in
that order.

John, I want to thank you for your help and patience. All I have learned in
Access has been self taught and I'm not the best teacher <grin>. I just
found out that a fellow ham is good with access. I am going to have him look
at what we've done and see if I'm following you correctly. I'll let you know
the outcome.

73,
Matt