Show records that dont equal todays date!

ninja_imp

Registered User.
Local time
Today, 11:05
Joined
Apr 11, 2006
Messages
50
One the face of it it sounds quite simple - i have a table that holds various bits of info about a customer but also a field that contains a holiday period - what i want to be able to do is show all records that do not equal todays date.

So if today was 11/04/06 - and one of my customers had 11/04/06
as one of his holiday days he would not appear on the report but all the others would.

Any help would be greatly appreciated

regards
 
There must be more to this than meets the eye else
fldDate <>Date()

You talked of a holiday period if this means that you have two dates giving start and end of holiday then
startdate > Date() Or enddate <Date()

Brian
 
hi thanks for the reply.

It is in fact as you say - the re is a from and till date.

My only problem is now getting it to not display that record while the current date is still between the from and till date.

Any ideas
 
So you need to know if the current date is in the Holiday period? Here is a function I wrote that you can use. It will return a True or -1 value if the current date is in the Holiday period and false or 0 if not.

Public Function IsHoliday(StartDate As Date, EndDate As Date) As Boolean

Dim dteStart, dteEnd, dteNow As Date
Dim strDayBtwn As String

dteStart = StartDate
dteEnd = EndDate
dteNow = Date

strDayBtwn = DateDiff("d", dteStart, dteEnd)

For X = 1 To strDayBtwn
If dteNow = dteStart Then
IsHoliday = True
GoTo Done
Else
dteStart = dteStart + 1
End If
Next X

IsHoliday = False
Done:

End Function

If you copy and paste that into a module you will be able to use the function in a query.
 
thats excellent - i'll give that a go and let you know how i get on

many thanks for your help
 
sorry - not to seem really thick but my two values

[Holiday_from]
[Holiday_Till]

Where would i put them in the above function?!!!!
 
You shold use the function is a query. Your query field should look like this
IsHoliday(Holiday_From,Holiday_Till)


Then in the criteria row you are going to want to put False or 0
 
this is perfect - thankyou for your help.

I have another query - i have a sub form in the main customer screen which has a small table holding the holiday dates - I have them linked by the custRef field but when i add a new record i get this error:

You can't assign a value to this object.
*the object may be a control on a read-only form
*the object may be on a form that is open in design view
* the value may be to largefor this field


When you click past the error it still holds the value but it is against a new CustRef ID and not the same as the main customer form which it should be?

Any ideas
 
It probably has to do with your relatinships. can you post your db?
 
Maybe the old grey matter has gone senile but in answer to your first quetion what is wrong with putting >Date() in the first criterua row under Holiday_From and <Date() in the second criteria row under Holiday_Till thus creating
Where Holiday_From> Date() OR Holiday_Till <Date()

If the holiday hasn't started or has already finished it cannot be active for Date()

Brian
 
Brianwarnock:
Im not sure -doing it that way on the first field worked fine but putting it on the 'till' field it didn't work - not sure if its because some of the fields are empty if there is no holiday. I tried it with <>Date() OR is Null but still didnt give accurate results.

KeithG:
I think i may have seen where i have gone worng - because i wanted the holiday section to be in datasheet view i run a query agaisnt the main customer database (Where the holiday dates are stored as well) so i could have a form that just pulls the holiday dates from the DB - hence now why i think it is not working - i need to create a new table for holidays and just reference it against the customer tbl via CustRef - I'll give that a go and let you know how i get on

Many thanks for yours and Brians Help
 
next conundrum - your function works fine but if they say have only one day and the to and from date is the same it doesnt recognise it and still gives a positive result.
 
You said that my approach did not work because some fields were empty, how do you want to handle empty fields?

Brian
 
i want all empty fields to be displayed- as this would mean here is no holiday set.

cheers
 
Here you go, replace the old IsHoliday function with this one.

Public Function IsHoliday(StartDate As Date, EndDate As Date) As Boolean

Dim dteStart, dteEnd, dteNow As Date
Dim strDayBtwn As String

dteStart = StartDate
dteEnd = EndDate
dteNow = Date

If dteStart = dteEnd Then
If dteStart = dteNow Then
IsHoliday = True
GoTo Done
Else
IsHoliday = False
GoTo Done
End If
End If


strDayBtwn = DateDiff("d", dteStart, dteEnd)

For X = 1 To strDayBtwn
If dteNow = dteStart Then
IsHoliday = True
GoTo Done
Else
dteStart = dteStart + 1
End If
Next X

IsHoliday = False
Done:

End Function
 
Then in the third row of the grid, assuming the first two rows are as above put Is Null under Holiday_From and in the 4th row put it under Holiday_till

This will give you a Where clause in the SQL view like

Code:
WHERE (((Table5.sdate)>Date())) OR (((Table5.edate)<Date())) OR (((Table5.sdate) Is Null)) OR (((Table5.edate) Is Null))

but with your names, as you can see I ran a simple test and it seems to work.

Brian
 
hi

just wanted to say cheers to all help on this - problem has now been resolved
 

Users who are viewing this thread

Back
Top Bottom