operator comparison

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can you show me how to compare the date I chose between two other dates. For
example,
SELECT * FROM tblPublish WHERE tblPublish!Id = '" & Me.[Id] & "' and
(tblPublish.StartDate>= #" & Me.[Date] & "# AND tblPublish.EndDate<= #" &
Me.[Date] & "#;"

I don't know if it is correct or not but I can not count the records after
these statement. I use ADO.
Thanks,
 
tblPublish.StartDate>= #" & Me.[Date] & "#

this translates to "StartDate is the same as, or after, Me.Date"

tblPublish.EndDate<= #" & Me.[Date] & "#;"

this translates to "EndDate is the same as, or before, Me.Date"

assuming that, in your table's records, the StartDate is always the same as,
or before, the EndDate, you'll never find a record matching

"StartDate is *after* Me.Date AND EndDate is *before* Me.Date

instead, try

tblPublish.StartDate<= #" & Me.[Date] & "# AND tblPublish.EndDate>= #" &
Me.[Date] & "#;"

which translates to "StartDate is the same as, or before, Me.Date and
EndDate is the same as, or after Me.Date". this is the same as saying "I
want to see the records where Me.Date falls between the StartDate and the
EndDate".

btw, if you really have a field in your table named Date, suggest you change
it. Date is a Reserved word in Access, and so shouldn't be used for anything
you name in the database.

hth
 
First thing is that your comparison is upside down. Second, don't use Date as
a field name. It is an Access reserved word and can cause problems. I see
you have it in brackets, but you can't necessarily depend on the brackets to
eliminate all ambiguity. Here is an easier way to do the comparison you are
trying for:

SELECT * FROM tblPublish WHERE tblPublish!Id = '" & Me.[Id] & "' and #" &
Me.[Date] & "# BETWEEN tblPublish.StartDate AND tblPublish.EndDate;"

I don't think you need the #s around the table fields if they are a Date
data type, but I am not 100% sure.
 
Hi experts:
Thank you for your tips. I will check and test them and will let you know
whatever comes out.

Thanks again,

tina said:
tblPublish.StartDate>= #" & Me.[Date] & "#

this translates to "StartDate is the same as, or after, Me.Date"

tblPublish.EndDate<= #" & Me.[Date] & "#;"

this translates to "EndDate is the same as, or before, Me.Date"

assuming that, in your table's records, the StartDate is always the same as,
or before, the EndDate, you'll never find a record matching

"StartDate is *after* Me.Date AND EndDate is *before* Me.Date

instead, try

tblPublish.StartDate<= #" & Me.[Date] & "# AND tblPublish.EndDate>= #" &
Me.[Date] & "#;"

which translates to "StartDate is the same as, or before, Me.Date and
EndDate is the same as, or after Me.Date". this is the same as saying "I
want to see the records where Me.Date falls between the StartDate and the
EndDate".

btw, if you really have a field in your table named Date, suggest you change
it. Date is a Reserved word in Access, and so shouldn't be used for anything
you name in the database.

hth


Tim said:
Can you show me how to compare the date I chose between two other dates. For
example,
SELECT * FROM tblPublish WHERE tblPublish!Id = '" & Me.[Id] & "' and
(tblPublish.StartDate>= #" & Me.[Date] & "# AND tblPublish.EndDate<= #" &
Me.[Date] & "#;"

I don't know if it is correct or not but I can not count the records after
these statement. I use ADO.
Thanks,
Click to expand...
Click to expand...
 
Definition of Expert from Klatuu's Intergalactic Dictionary:

Ex - Has been
spurt - A drip under pressure

Tim said:
Hi experts:
Thank you for your tips. I will check and test them and will let you know
whatever comes out.

Thanks again,

tina said:
tblPublish.StartDate>= #" & Me.[Date] & "#

this translates to "StartDate is the same as, or after, Me.Date"

tblPublish.EndDate<= #" & Me.[Date] & "#;"

this translates to "EndDate is the same as, or before, Me.Date"

assuming that, in your table's records, the StartDate is always the same as,
or before, the EndDate, you'll never find a record matching

"StartDate is *after* Me.Date AND EndDate is *before* Me.Date

instead, try

tblPublish.StartDate<= #" & Me.[Date] & "# AND tblPublish.EndDate>= #" &
Me.[Date] & "#;"

which translates to "StartDate is the same as, or before, Me.Date and
EndDate is the same as, or after Me.Date". this is the same as saying "I
want to see the records where Me.Date falls between the StartDate and the
EndDate".

btw, if you really have a field in your table named Date, suggest you change
it. Date is a Reserved word in Access, and so shouldn't be used for anything
you name in the database.

hth


Tim said:
Can you show me how to compare the date I chose between two other dates. For
example,
SELECT * FROM tblPublish WHERE tblPublish!Id = '" & Me.[Id] & "' and
(tblPublish.StartDate>= #" & Me.[Date] & "# AND tblPublish.EndDate<= #" &
Me.[Date] & "#;"

I don't know if it is correct or not but I can not count the records after
these statement. I use ADO.
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
It's funny about the definition of the word "expert", but you guys did help
me in this issue. I use the "between" clause thank to Klatuu and Tina pointed
out why my code is wrong. Also, the pound key (#) is not needed!

This part of the program has been worked, i.e., I am able to count the
records. Well, there is still a lot of more work but thanks again to help me
go through.
Tim

Klatuu said:
Definition of Expert from Klatuu's Intergalactic Dictionary:

Ex - Has been
spurt - A drip under pressure

Tim said:
Hi experts:
Thank you for your tips. I will check and test them and will let you know
whatever comes out.

Thanks again,

tina said:
tblPublish.StartDate>= #" & Me.[Date] & "#

this translates to "StartDate is the same as, or after, Me.Date"

tblPublish.EndDate<= #" & Me.[Date] & "#;"

this translates to "EndDate is the same as, or before, Me.Date"

assuming that, in your table's records, the StartDate is always the same as,
or before, the EndDate, you'll never find a record matching

"StartDate is *after* Me.Date AND EndDate is *before* Me.Date

instead, try

tblPublish.StartDate<= #" & Me.[Date] & "# AND tblPublish.EndDate>= #" &
Me.[Date] & "#;"

which translates to "StartDate is the same as, or before, Me.Date and
EndDate is the same as, or after Me.Date". this is the same as saying "I
want to see the records where Me.Date falls between the StartDate and the
EndDate".

btw, if you really have a field in your table named Date, suggest you change
it. Date is a Reserved word in Access, and so shouldn't be used for anything
you name in the database.

hth


Can you show me how to compare the date I chose between two other dates.
For
example,
SELECT * FROM tblPublish WHERE tblPublish!Id = '" & Me.[Id] & "' and
(tblPublish.StartDate>= #" & Me.[Date] & "# AND tblPublish.EndDate<= #" &
Me.[Date] & "#;"

I don't know if it is correct or not but I can not count the records after
these statement. I use ADO.
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
you're welcome :)


Tim said:
Hi experts:
Thank you for your tips. I will check and test them and will let you know
whatever comes out.

Thanks again,

tina said:
tblPublish.StartDate>= #" & Me.[Date] & "#

this translates to "StartDate is the same as, or after, Me.Date"

tblPublish.EndDate<= #" & Me.[Date] & "#;"

this translates to "EndDate is the same as, or before, Me.Date"

assuming that, in your table's records, the StartDate is always the same as,
or before, the EndDate, you'll never find a record matching

"StartDate is *after* Me.Date AND EndDate is *before* Me.Date

instead, try

tblPublish.StartDate<= #" & Me.[Date] & "# AND tblPublish.EndDate>= #" &
Me.[Date] & "#;"

which translates to "StartDate is the same as, or before, Me.Date and
EndDate is the same as, or after Me.Date". this is the same as saying "I
want to see the records where Me.Date falls between the StartDate and the
EndDate".

btw, if you really have a field in your table named Date, suggest you change
it. Date is a Reserved word in Access, and so shouldn't be used for anything
you name in the database.

hth


Tim said:
Can you show me how to compare the date I chose between two other
Click to expand...
dates.
For
example,
SELECT * FROM tblPublish WHERE tblPublish!Id = '" & Me.[Id] & "' and
(tblPublish.StartDate>= #" & Me.[Date] & "# AND tblPublish.EndDate<= #" &
Me.[Date] & "#;"

I don't know if it is correct or not but I can not count the records after
these statement. I use ADO.
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
I have another question. My db is the MS mdb front-end and SQL Server
back-end. I'd like to make a duplicate db to play with it. Do you think it's
a good idea? Is the original one likely to be corrupted?
Also, I tested this morning and it did show the records, but now I test it
again, it shows zero (0) record. Do you know why?
Thanks
 
well, there are certain differences between an Access database that contain
"its' own data", and using an Access database to work with records stored in
SQL Server. i have no experience in working with SQL Server at all, so while
i'm aware that there are differences, i'm in no position to comment or
advise you on specifics.

recommend you start a new newsgroup thread re this question. make sure you
declare immediately that you're "fronting" a SQL Server database with
Access, so that only folks with the requisite experience will try to help
you. good luck.
 
Back
Top