Can anybody help? (originally posted in macromedia.dreamweaver)

DTB-K wrote:
> Steve,
>
> Thanks for that.
>
> I tried your first suggestion and it appears to work like a charm.
>
> Thanks for your time and effort in assisting me to resolve this - it was/is very much appreciated.

Excellent, glad I could help!

Steve

Steve,

Thanks for that.

I tried your first suggestion and it appears to work like a charm.

Thanks for your time and effort in assisting me to resolve this - it was/is very much appreciated.

Regards

DTBK

"Dooza" <doozadooza@gmail.com> wrote in message news:g16re1$914$1@forums.macromedia.com...
> DTB-K wrote:
>> Dooza,
>>
>> Why on earth didn't I think of that??
>>
>> Thanks for that, it helped to indentify what the problem is and it appears that it's with my recordset which is supposed to be
>> filtering the database of existing offers received.
>>
>> The SQL Statement is as follows...
>>
>> ("var_CUSID" is bound after filtering the database of registered users with Session("MM_Username") and then binding the resulting
>> ID value to the variable (UserID) and then specifying the value of var_CUSID as (UserID))
>>
>> SELECT *
>> FROM tbl_Offers
>> WHERE CusID = var_CUSID AND OfferStatus = 'Status 1' OR OfferStatus = 'Status 2' OR OfferStatus = 'Status 3'
>> ORDER BY DateOfOffer DESC
>
> First thing to try is:
>
> WHERE CusID = var_CUSID AND (OfferStatus = 'Status 1' OR OfferStatus = 'Status 2' OR OfferStatus = 'Status 3')
>
> Or you could try:
>
> WHERE (CusID = var_CUSID AND OfferStatus = 'Status 1') OR (CusID = var_CUSID AND OfferStatus = 'Status 2') OR (CusID = var_CUSID
> AND OfferStatus = 'Status 3')
>
> Or maybe this:
>
> WHERE CusID = var_CUSID AND OfferStatus IN ('Status 1','Status 2','Status 3')
>
> Steve

DTB-K wrote:
> Dooza,
>
> Why on earth didn't I think of that??
>
> Thanks for that, it helped to indentify what the problem is and it appears that it's with my recordset which is supposed to be
> filtering the database of existing offers received.
>
> The SQL Statement is as follows...
>
> ("var_CUSID" is bound after filtering the database of registered users with Session("MM_Username") and then binding the resulting ID
> value to the variable (UserID) and then specifying the value of var_CUSID as (UserID))
>
> SELECT *
> FROM tbl_Offers
> WHERE CusID = var_CUSID AND OfferStatus = 'Status 1' OR OfferStatus = 'Status 2' OR OfferStatus = 'Status 3'
> ORDER BY DateOfOffer DESC

First thing to try is:

WHERE CusID = var_CUSID AND (OfferStatus = 'Status 1' OR OfferStatus =
'Status 2' OR OfferStatus = 'Status 3')

Or you could try:

WHERE (CusID = var_CUSID AND OfferStatus = 'Status 1') OR (CusID =
var_CUSID AND OfferStatus = 'Status 2') OR (CusID = var_CUSID AND
OfferStatus = 'Status 3')

Or maybe this:

WHERE CusID = var_CUSID AND OfferStatus IN ('Status 1','Status
2','Status 3')

Steve

Dooza,

Why on earth didn't I think of that??

Thanks for that, it helped to indentify what the problem is and it appears that it's with my recordset which is supposed to be
filtering the database of existing offers received.

The SQL Statement is as follows...

("var_CUSID" is bound after filtering the database of registered users with Session("MM_Username") and then binding the resulting ID
value to the variable (UserID) and then specifying the value of var_CUSID as (UserID))

SELECT *
FROM tbl_Offers
WHERE CusID = var_CUSID AND OfferStatus = 'Status 1' OR OfferStatus = 'Status 2' OR OfferStatus = 'Status 3'
ORDER BY DateOfOffer DESC

Obviously what I'm trying to achieve is to only pull back the offers/records which match the User ID, but then there's actually 4
possible status' of an offer, I only want to pull back those records which match one of 3 status'.


What appears to be happening is that my OR statements are in someway over-riding everything else as it's pulling back records which
do not match the UserID.

I've tried filtering on just the User ID (E.g. WHERE CusID = var_CUSID) and this works fine.

I've tried filtering on just the user ID and one possible Status (E.g. WHERE CusID = var_CUSID AND OfferStatus = 'Status 1') and
this also works fine.

As soon as I put the OR statement to try to account for a different status (but same User ID) it starts to bring records back
regardless as to whether they match the UserID.

Any suggestions as to how I might correct this?

Regards

DTBK


"Dooza" <doozadooza@gmail.com> wrote in message news:g13apo$cud$1@forums.macromedia.com...
> DTB-K wrote:
>> Hi Guys and Gals,
>>
>> I need some help determining a problem with some code I'm attempting to use.
>>
>> It's probably something obvious that I'm missing... what I'm trying to achieve is this
>>
>> * - Has the user submitted any previous offers?
>> * - If they HAVE, was their most recent offer submitted in the last 24 hours?
>> * - If their most recent offer was submitted in the last 24 hours then redirect the user to the page "Error.asp"
>>
>> * - If the user HAS NOT submitted an offer in the last 24 hours OR their most recent offer was submitted MORE than 24 hours ago,
>> allow the rest of this page to load and a new offer to be submitted.
>>
>>
>> This is the actual code I'm using:
>>
>> <%
>>
>> If Rs_ExistingOffers.EOF And Rs_ExistingOffers.BOF Then
>> 'If There are NO Existing Offers
>> 'Do nothing and let the page load.
>> Else
>> If Not Rs_ExistingOffers.EOF Or Not Rs_ExistingOffers.BOF Then
>> 'If there ARE existing offers, now we check to see if any of
>> 'them are from within the last 24 hours
>> HoursSinceLastQuote = DateDiff("h",(Rs_ExistingOffers.Fields.Item("DateOfOffer").Value),Now())
>> 'If the most recent previous offer was within the last 24
>> 'hours, prevent a further offer from being submitted.
>> If (HoursSinceLastQuote) < 24 Then
>> Response.Redirect("Error.asp")
>> End If
>> End if
>> End If
>>
>> %>
>>
>> Unfortunately, whether there is an existing offer in the database or not, the page redirects to "Error.asp" regardless.
>>
>> FYI, the recordset "Rs_ExistingOffers" is being filtered by way of the Customer ID (CusID) (which is a by-product of the
>> customer's
>> Username and Session("MM_Username") filtering the database of users for authentication), and then ordering any records it finds
>> in Rs_ExistingOffers that
>> match the CusID in decending order (I.e. most recent first).
>>
>> Please, can anyone see what it is that I'm doing wrong?
>
> First thing you want to do is debug it, so you can see what is really going on:
>
> If Rs_ExistingOffers.EOF And Rs_ExistingOffers.BOF Then
> Response.Write("No offers")
> Else
> If Not Rs_ExistingOffers.EOF Or Not Rs_ExistingOffers.BOF Then
> Response.Write("Offers")
> 'them are from within the last 24 hours
> HoursSinceLastQuote = DateDiff("h",(Rs_ExistingOffers.Fields.Item("DateOfOffer").Value),Now())
> 'Lets see how many hours
> Response.Write("Hours since last quote: " & HoursSinceLastQuote)
> 'If the most recent previous offer was within the last 24
> 'hours, prevent a further offer from being submitted.
> If (HoursSinceLastQuote) < 24 Then
> Response.Write("Error")
> End If
> End if
> End If

DTB-K wrote:
> Hi Guys and Gals,
>
> I need some help determining a problem with some code I'm attempting to use.
>
> It's probably something obvious that I'm missing... what I'm trying to achieve is this
>
> * - Has the user submitted any previous offers?
> * - If they HAVE, was their most recent offer submitted in the last 24 hours?
> * - If their most recent offer was submitted in the last 24 hours then redirect the user to the page "Error.asp"
>
> * - If the user HAS NOT submitted an offer in the last 24 hours OR their most recent offer was submitted MORE than 24 hours ago,
> allow the rest of this page to load and a new offer to be submitted.
>
>
> This is the actual code I'm using:
>
> <%
>
> If Rs_ExistingOffers.EOF And Rs_ExistingOffers.BOF Then
> 'If There are NO Existing Offers
> 'Do nothing and let the page load.
> Else
> If Not Rs_ExistingOffers.EOF Or Not Rs_ExistingOffers.BOF Then
> 'If there ARE existing offers, now we check to see if any of
> 'them are from within the last 24 hours
> HoursSinceLastQuote = DateDiff("h",(Rs_ExistingOffers.Fields.Item("DateOfOffer").Value),Now())
> 'If the most recent previous offer was within the last 24
> 'hours, prevent a further offer from being submitted.
> If (HoursSinceLastQuote) < 24 Then
> Response.Redirect("Error.asp")
> End If
> End if
> End If
>
> %>
>
> Unfortunately, whether there is an existing offer in the database or not, the page redirects to "Error.asp" regardless.
>
> FYI, the recordset "Rs_ExistingOffers" is being filtered by way of the Customer ID (CusID) (which is a by-product of the customer's
> Username and Session("MM_Username") filtering the database of users for authentication), and then ordering any records it finds in
> Rs_ExistingOffers that
> match the CusID in decending order (I.e. most recent first).
>
> Please, can anyone see what it is that I'm doing wrong?

First thing you want to do is debug it, so you can see what is really
going on:

If Rs_ExistingOffers.EOF And Rs_ExistingOffers.BOF Then
Response.Write("No offers")
Else
If Not Rs_ExistingOffers.EOF Or Not Rs_ExistingOffers.BOF Then
Response.Write("Offers")
'them are from within the last 24 hours
HoursSinceLastQuote =
DateDiff("h",(Rs_ExistingOffers.Fields.Item("DateOfOffer").Value),Now())
'Lets see how many hours
Response.Write("Hours since last quote: " & HoursSinceLastQuote)
'If the most recent previous offer was within the last 24
'hours, prevent a further offer from being submitted.
If (HoursSinceLastQuote) < 24 Then
Response.Write("Error")
End If
End if
End If