Please help with query criteria

[DaGoon]

To All,

Can I please trouble the group for some help with a query.

I'm querying a table named "tblezstops" (without quotes), this table
has a field named "stoptime" (without quotes). The format of the data
in the "stoptime" field is exactly as follows (without quotes):
"11/22/2005 2:45:59 PM"

I need the query to retrieve records that have a date one day earlier
then the date I run the query and I also need the same query to
retrieve records between...lets say 9:00 AM through 10:00 PM.

What would the correct syntax of the criteria statement be in the
"stoptime" field of the query? With my limited experience I tried the
following with no luck:

Date()- 1 Between #9:00:00 AM# And #10:00:00 PM#

I would greatly appreciate any help anyone would have to offer.

 

[Randy Harris]

To All,

Can I please trouble the group for some help with a query.

I'm querying a table named "tblezstops" (without quotes), this table
has a field named "stoptime" (without quotes). The format of the data
in the "stoptime" field is exactly as follows (without quotes):
"11/22/2005 2:45:59 PM"

I need the query to retrieve records that have a date one day earlier
then the date I run the query and I also need the same query to
retrieve records between...lets say 9:00 AM through 10:00 PM.

What would the correct syntax of the criteria statement be in the
"stoptime" field of the query? With my limited experience I tried the
following with no luck:

Date()- 1 Between #9:00:00 AM# And #10:00:00 PM#

I would greatly appreciate any help anyone would have to offer.

In the table, is stoptime a Date/Time field or a Text field?

 

[DaGoon]

stoptime is a Date/Time field.

Thanks for your response!

 

[Douglas J Steele]

Add a couple of computed fields to your query by typing the following into
blank cells on the Field row:

StoptimeDate: DateValue([stoptime])
StoptimeTime: TimeValue([stoptime])

Now, in the cell on the criteria row under "StoptimeDate", put Date() -1 and
in the cell under StoptimeDate on the same criteria row, put Between
#9:00:00 AM# And #10:00:00 PM#

Another alternative would be to use

Between ((Date() - 1) + #9:00:00 AM#) And ((Date() - 1) + #10:00:00 PM#)

 

[Guest]

Note that a BETWEEN….AND operation is inclusive, so if you want the range to
be exclusive of the start or end time and rows with a StopTime of exactly
9.00 AM or exactly 10.00 PM excluded use, for the former:

WHERE StopTime > DATE() - 1 + #9:00:00 AM#
AND StopTime <= DATE() - 1 + #10:00:00 PM#

or for the latter:

WHERE StopTime >= DATE() - 1 + #9:00:00 AM#
AND StopTime < DATE() - 1 + #10:00:00 PM#

 

[DaGoon]

Douglas and the rest of the group,

I used your suggestion and the following worked just fine:

Between ((Date()-1)+#8:30:01 PM#) And ((Date())+#6:59:59 AM#)

I greatly appreciate your, and everybody's assistance!

Now I would like to go a step further but, once again, I am struggling
with the syntax for the criteria expression.

I have a form named "frmPickDate", without the quotes. On that form I
have an ActiveX calendar control named "pkDate", without the quotes. I
would like to have this form pass along the date that a user selects.
I am accomplishing this via a command button with some VBA code that
prints a report that uses my query as it's source. I have a handle on
that but, like I previously stated, I am struggling with the syntax of
the criteria expression in the query. I tried the following but, no
luck:

Between (([Forms]![frmPickDate]![PkDate]-1)+#8:30:01 PM#) And
(([Forms]![frmPickDate]![PkDate])+#6:59:59 AM#)

Can I please trouble the group to point me in the right direction as
to what the proper syntax should be for this criteria expression?

Thanks again!

 

[Douglas J Steele]

I assume the form is open when the query's trying to run.

What specific error are you getting?

See whether the following works any better:

Between (DateAdd("d", -1, [Forms]![frmPickDate]![PkDate])+#8:30:01 PM#) And
([Forms]![frmPickDate]![PkDate]+#6:59:59 AM#)

If that doesn't work, then try:

Between (DateAdd("d", -1, CDate([Forms]![frmPickDate]![PkDate]))+#8:30:01
PM#) And
(CDate([Forms]![frmPickDate]![PkDate])+#6:59:59 AM#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas and the rest of the group,

I used your suggestion and the following worked just fine:

Between ((Date()-1)+#8:30:01 PM#) And ((Date())+#6:59:59 AM#)

I greatly appreciate your, and everybody's assistance!

Now I would like to go a step further but, once again, I am struggling
with the syntax for the criteria expression.

I have a form named "frmPickDate", without the quotes. On that form I
have an ActiveX calendar control named "pkDate", without the quotes. I
would like to have this form pass along the date that a user selects.
I am accomplishing this via a command button with some VBA code that
prints a report that uses my query as it's source. I have a handle on
that but, like I previously stated, I am struggling with the syntax of
the criteria expression in the query. I tried the following but, no
luck:

Between (([Forms]![frmPickDate]![PkDate]-1)+#8:30:01 PM#) And
(([Forms]![frmPickDate]![PkDate])+#6:59:59 AM#)

Can I please trouble the group to point me in the right direction as
to what the proper syntax should be for this criteria expression?

Thanks again!

Add a couple of computed fields to your query by typing the following into
blank cells on the Field row:

StoptimeDate: DateValue([stoptime])
StoptimeTime: TimeValue([stoptime])

Now, in the cell on the criteria row under "StoptimeDate", put Date() -1 and
in the cell under StoptimeDate on the same criteria row, put Between
#9:00:00 AM# And #10:00:00 PM#

Another alternative would be to use

Between ((Date() - 1) + #9:00:00 AM#) And ((Date() - 1) + #10:00:00 PM#)

 

[DaGoon]

Douglas,

The first of your two solutions worked just fine. What is the
difference between the first and second solutions?

Thank you once again for bailing me out!

Regards,

DaGoon

 

[Douglas J. Steele]

The first assumes that Access will treat what's in PkDate as a date. The
second uses the CDate function to explicitly convert it to a date, rather
than relying on Access doing that for you. It shouldn't be necessary, but
given a choice, it's always better to use explicit conversions, rather than
relying on Access "guessing" correctly.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

The first of your two solutions worked just fine. What is the
difference between the first and second solutions?

Thank you once again for bailing me out!

Regards,

DaGoon

I assume the form is open when the query's trying to run.

What specific error are you getting?

See whether the following works any better:

Between (DateAdd("d", -1, [Forms]![frmPickDate]![PkDate])+#8:30:01 PM#)
And
([Forms]![frmPickDate]![PkDate]+#6:59:59 AM#)

If that doesn't work, then try:

Between (DateAdd("d", -1, CDate([Forms]![frmPickDate]![PkDate]))+#8:30:01
PM#) And
(CDate([Forms]![frmPickDate]![PkDate])+#6:59:59 AM#)

 

[DaGoon]

Douglas,

Can I trouble you with another question?

I have a textbox on a report that has the following as it's control
source:

=[Forms]![frmPickDayReport]![txtStartDate] + " through " +
[Forms]![frmPickDayReport]![txtEndDate] + "."

I would like the textbox to display as follows:

Friday, November 27, 2005 through Monday, November, 29 2005.

I know I can format the date with a format command but, I'm not
exactly sure what the expression would be. The commas are not
important but I would like to keep the long date format.

I hope I am not imposing too much on you but I greatly appreciate your
help!

PS- Yes, the form is open but visable= false

Regards,

Dagoon

 

[Douglas J. Steele]

=Format([Forms]![frmPickDayReport]![txtStartDate], "dddd, mmmm dd yyyy") & "
through " &
Format([Forms]![frmPickDayReport]![txtEndDate], "dddd, mmmm dd yyyy") & "."

Please note that I changed your concatenation character from + to &.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Can I trouble you with another question?

I have a textbox on a report that has the following as it's control
source:

=[Forms]![frmPickDayReport]![txtStartDate] + " through " +
[Forms]![frmPickDayReport]![txtEndDate] + "."

I would like the textbox to display as follows:

Friday, November 27, 2005 through Monday, November, 29 2005.

I know I can format the date with a format command but, I'm not
exactly sure what the expression would be. The commas are not
important but I would like to keep the long date format.

I hope I am not imposing too much on you but I greatly appreciate your
help!

PS- Yes, the form is open but visable= false

Regards,

Dagoon

The first assumes that Access will treat what's in PkDate as a date. The
second uses the CDate function to explicitly convert it to a date, rather
than relying on Access doing that for you. It shouldn't be necessary, but
given a choice, it's always better to use explicit conversions, rather
than
relying on Access "guessing" correctly.

 

[DaGoon]

Douglas,

I'm sorry to bother you with the previous post but, I was able to find
the answer to this via another post here in the group. I was able to
accomplish what I was trying to do with:

=Format(Forms!frmPickWeekendReport!txtStartDate,"Long Date")+" through
"+Format(Forms!frmPickWeekendReport!txtEndDate,"Long Date")+" ."

Why use & instead of +?

Thanks again!!!

 

[Joe Cilinceon]

Douglas,

I'm sorry to bother you with the previous post but, I was able to find
the answer to this via another post here in the group. I was able to
accomplish what I was trying to do with:

=Format(Forms!frmPickWeekendReport!txtStartDate,"Long Date")+" through
"+Format(Forms!frmPickWeekendReport!txtEndDate,"Long Date")+" ."

Why use & instead of +?

Thanks again!!!

Well i don't think that will work. As I understand it the + sign is for
addition of numbers the & is for combining fields or strings.

 

[Vincent Johns]

Well i don't think that will work. As I understand it the + sign is for
addition of numbers the & is for combining fields or strings.

Although you are correct, in some contexts (such as in MS Jet SQL, the
SQL dialect that Access uses) either "&" or "+" can be used to catenate
strings. I prefer "&" to do that, but either one works.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Joe Cilinceon]

Thanks Vincent I stand corrected. g

Although you are correct, in some contexts (such as in MS Jet SQL, the
SQL dialect that Access uses) either "&" or "+" can be used to
catenate strings. I prefer "&" to do that, but either one works.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[DaGoon]

Douglas,

I hate to change the topic but, can I also trouble you for some
assistance regarding the following?

Is there any way I can program logging into a separate SQL database
via my MS access database, possibly with a command button? My MS
Access database contains linked tables to the SQL database and I have
to enter a username and password prior to accessing any of the data in
the linked tables. I have been doing this manually but I wish to
automate the process. I am prompted to login via the dialog boxes
contained in the attached screen shots.

I need to accomplish this task via the above described method because
the username and password are generic and our companies IT department
uses them to access many systems throughout our corporation.

Again, I greatly appreciate any help you have to offer!

 

[Douglas J. Steele]

Although you are correct, in some contexts (such as in MS Jet SQL, the SQL
dialect that Access uses) either "&" or "+" can be used to catenate
strings. I prefer "&" to do that, but either one works.

Actually, there's a subtle difference between using + and & when it comes to
concatenating text in terms of how they handle Null values.

"something" + Null (or Null + "something") will be Null.

"something" & Null (or Null & "something") will be "something"

This difference can be used effectively. If, for example, you've got two
lines of Address information and you're trying to concatenate your data as:

Address1 & vbCrLf & Address2 & vbCrLf & City & ", " & State

so that the address appears on 3 separate lines, the above will have a blank
link in between when Address2 is Null.

Address1 & vbCrLf & (Address2 + vbCrLf) & City & ", " & State

on the other hand, will only use 2 lines if Address2 is Null.

 

[Vincent Johns]

Douglas,

I hate to change the topic but, can I also trouble you for some
assistance regarding the following?

Is there any way I can program logging into a separate SQL database
via my MS access database, possibly with a command button? My MS
Access database contains linked tables to the SQL database and I have
to enter a username and password prior to accessing any of the data in
the linked tables. I have been doing this manually but I wish to
automate the process. I am prompted to login via the dialog boxes
contained in the attached screen shots.

I need to accomplish this task via the above described method because
the username and password are generic and our companies IT department
uses them to access many systems throughout our corporation.

Again, I greatly appreciate any help you have to offer!

You might be able to accomplish what you wish via judicious use of the
"SendKeys" action, if the window that pops up doesn't expose its objects
to you. One problem with SendKeys is that, if the environment changes a
bit, such as that some other consumer of keystrokes shows up at the
wrong time, your procedure won't do exactly what you expected.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[DaGoon]

Vincent,

I will take the sendkeys route. Thanks for your help.

Currently when I try to access any data in the linked SQL tables I am
prompted for a username and password. At that point I manually type
them in. My next question is...is the any code and/or command that I
can use to, for a lack of a better term, request a login to the SQL
database? I would like to do it that way instead of first trying to
access the data and then being prompted for a username and password.
If there is, I will just create a form with a command button that I
can use to run a macro that contains the proper sendkeys.

Thanks again!

 

[Vincent Johns]

Vincent,

I will take the sendkeys route. Thanks for your help.

Be cautious when using SendKeys. Since it generates a password which it
is in your interest to keep private, you also need to protect the code
that does that. For example, it might not be wise to email a copy of
your program to someone.

Currently when I try to access any data in the linked SQL tables I am
prompted for a username and password. At that point I manually type
them in. My next question is...is the any code and/or command that I
can use to, for a lack of a better term, request a login to the SQL
database? I would like to do it that way instead of first trying to
access the data and then being prompted for a username and password.
If there is, I will just create a form with a command button that I
can use to run a macro that contains the proper sendkeys.

Thanks again!

If you're referring to SQL Server, I'm afraid I'm not familiar enough
with it to advise you (though what you're suggesting seems reasonable).
If you can't find the answer in the SQL Server help, I suggest you
re-post this message in a new thread. (Actually, posting on an SQL
Server newsgroup might be more effective.) My answering you here may
have dissuaded other people from posting their answers to your question,
and if you post your question again, someone who knows the answer may
reply.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.