Problem comparing dates in If sentence

[Inkognito]

Hi Again

I have another If-related problem...

If rst!Dato >= Tekst27 Then
   If rst!Dato <= Tekst29 Then TextFile.WriteLine (rst!Dato - rst!Medlemsnr)
End If

I am trying to make an If sentence compare if the field [Dato] from a table I have is greater than the Text field "Tekst27" and smaller than "Tekst29".

I have checked (by using msgbox) that both rst!Dato and Tekst27/29 returns a valid date (DD.MM.YYYY).

Still these If statements are not triggered.... Why not?

 

[namliam]

The better way to code this ...
If rst!Dato >= Tekst27 and rst!Dato <= Tekst29 then

Any dates are to have a US date format, MM/DD/YYYY, not the european format... that is probably giving you the problem.

It is ussually best to
1) Use a date picker to have users enter dates
2) Enter dates as seperate values Day, Month, Year then use DateSerial to make it a date
3) Dont use the default names (Tekst27) in any fields you make... give them usefull names like txtDatoStart and txtDatoEnd

 

[Inkognito]

Yeah, I was looking for an If sentence like that, I tried "&" without any luck

I don't think there is any problem with the Date format, as it is comparing a text field where the user enters DD.MM.YYYY with a field from the table which i know is also DD.MM.YYYY.

Msgbox(Tekst27) and Msgbox(rst!Dato) returns 01.01.2008 and 05.04.2008, so I don't see why it doesn't trigger.

 

[namliam]

if your Dato field is a date field then my earlier post is relevant, despite the looks.... it is required in US format.

If your Dato field is a text field... your design if flawed, you need to have dates stored as dates, otherwize you run into this kind of problems...
Teksts make 01.12.2008 be less than 02.01.2008 because they sort by characters.... i.e. 01 is less than 02. Once your Dato field is a date/time field my first statement is valid yet again

 

[Inkognito]

Yeah, that makes sense, changed it to a Date field now

And you are absolutely right about the sorting by characters, as this isnt working:

If "05.04.2008" >= "01.01.2009" And "05.04.2008" <= "01.01.2009" Then
MsgBox "check"
TextFile.WriteLine (rst!Medlemsnr)
End If

But this is:

If "05.04.2008" >= "01.01.2009" And "05.04.2008" <= "05.04.2009" Then
MsgBox "check"
TextFile.WriteLine (rst!Medlemsnr)
End If

This isnt working either though:

Private Sub kommando26_click()
If "05/04/2008" >= "01/01/2009" And "05/04/2008" <= "01/01/2009" Then
MsgBox "check"
TextFile.WriteLine (rst!Medlemsnr)
End If
End Sub

So What can i do to force it to sort by Date?

 

[Inkognito]

I found a solution

Converting the dates to general numbers seem to work, but will this make correct sorting?

If (Format(rst!Dato, "General Number")) >= (Format(Tekst27, "General Number")) And (Format(rst!Dato, "General Number")) <= (Format(Tekst29, "General Number")) Then
TextFile.WriteLine (rst!Medlemsnr)
End If

 

[raskew]

Hi -

Converting the dates to general numbers seem to work

What you are referring to as "dates" are in fact strings, i.e. surrounded by " ".

When dealing with dates, they need to be enclosed with # #, e.g. #01/01/2009# versus "01/01/2009"

Note the difference in this example from the debug (immediate) window:

? "05.04.2008" >= "01.01.2009"
True

? #05/04/2008# >= #01/01/2009#
False

HTH - Bob

 

[namliam]

If "05.04.2008" >= "01.01.2009" And "05.04.2008" <= "01.01.2009" Then

Wouldnt be right even if they were dates

This isnt working either though:

Private Sub kommando26_click()
If "05/04/2008" >= "01/01/2009" And "05/04/2008" <= "01/01/2009" Then

Because these are NOT dates, anything between "" is a text, dates are enclosed by ## instead.

To convert strings, as I posted earlier, to dates use DateSerial if you have seperate input of Day Month and Year.
If you have one field, use CDate... But that again requires the format to be US!!!!!
Or use a datepicker to prevent the problem in the first place.

P.S. Bob got it right as well

P.P.S. When you post code please enclose the code in [ code] and [ /code] without the spaces.

 

[Inkognito]

Hahaha! Great! That solved it! I just wish i knew a few hours earlier

I've been sitting for hours programming these lines into my database

If (Format(Format(rst!Dato, "mm/dd/yyyy"), "General Number")) >= (Format(Format(Tekst57, "mm/dd/yyyy"), "General Number")) And (Format(Format(rst!Dato, "mm/dd/yyyy"), "General Number")) <= (Format(Format(Tekst58, "mm/dd/yyyy"), "General Number")) Then
  TextFile.WriteLine (rst!Dato) & " - " & (rst!Medlemsnr21)
  End If

Thx!

 

[Inkognito]

Dammit, I see now that it is still a bit wrong cause of the European date format!

It isn't an option to have my users enter dates in US format, and the table of which I am comparing the dates against, also must have European format

So is there a function to convert the dates from European to US format?

I tried googling this, but I couldn't find a good solution...

I thought this did it, but obvioulsy it didn't:

Format(rst!Dato, "mm/dd/yyyy")

 

[namliam]

Hahaha! Great! That solved it!

No no no.... Do you read my post(s) at all??? CDate or DateSerail...

Cdate or DateSerial

CDate or Dateserial

*argh*

Dammit, I see now that it is still a bit wrong cause of the European date format!

Finaly ??? LISTEN TO ME!

Access is US FORMAT BASSED, despite how things look once you look at the details... it is US FORMAT

Access is US DATE FORMAT!

It isn't an option to have my users enter dates in US format,

Offcourse it isnt, that is why you need a Date picker control OR use CDate OR Dateserail to take the Euro format, take it appart and put it back together in the US format.

So is there a function to convert the dates from European to US format?

No... not directly, unless CDate or Dateserial.... better yet use a DATEPICKER control (a calander your users can click)

Format(rst!Dato, "mm/dd/yyyy")

Format changes a real date to a text not the other way around....

 

[Inkognito]

Hahahaha

Yeah, yeah, i get it

It's faster for my users to enter the dates manually than to use a date picker, so i will stick to the hard way

I used this:

DatoUS = Chr(35) & Mid$(rst!Dato, 4, 3) & Left$(rst!Dato, 3) & Right$(rst!Dato, 4) & Chr(35)

And it works like a dream

Thx again

 

[namliam]

No NO NO!!!!

I dont see a Cdate or a DateSerial <<== GET IT??? Use one of these here functions!!!!
DateSerial would be prefered IMHO, if you dont use one of these functions you are relying on access to do 'implicit' i.e. not coded conversions. That means it has to do the thinking for you.... Implicit conversions are the biggest curse for any database as they rely on 'computer logic' to get it right and sometimes, offcourse those times when you least want it, it will make the wrong choice.

Conversion functions are there for a reason! TO BE USED... so use them already...

Also you will want to change your formula... This one will work for 15/01/2009, but what happens if someone enters 15/1/09 or 1/2/09 (1 feb 2009).

 

[Inkognito]

Yeah, I also just figured out that it still didn't work perfectly.... Seems i need some kind of function... Cdate or something

I have now added Cdate to my script

Tekst27ustemp = Mid$(Tekst27, 4, 3) & Left$(Tekst27, 3) & Right$(Tekst27, 4)
Tekst29ustemp = Mid$(Tekst29, 4, 3) & Left$(Tekst29, 3) & Right$(Tekst29, 4)
Tekst27us = CDate(Tekst27ustemp)
Tekst29us = CDate(Tekst29ustemp)

Hurray! This time i really think it is perfect

 

[raskew]

No! You may recall seeing "DATESERIAL" somewhere. It works like this:

x = "16.01.2009"
? x
16.01.2009

y = dateserial(right(x,4), mid(x, 4,2), left(x,2))
? y
1/16/2009

You can read all about it in your help file.

Note that I didn't include the separators (.) in the process. DateSerial()
will insert them (/) for you.

Bob

 

[namliam]

DateSerial would be prefered

Though it a personal taste... I find it generaly more easy to work with, as long as you are using one of the conversion functions....

Offcourse you are reliant upon the data entry of DD/MM/YYYY and any other entry will fail... But if that is OK, looks good!

Now all you need to do is change Tekst27 to StartDate or something meaningfull like that and do same to Tekst29 and all you tekst fields on all forms!

Good luck and happy to help

 

[Inkognito]

Okay Trying DateSerial now

Something like this then? :

tekst27us = DateSerial(Right(Tekst27, 4), Mid(Tekst27, 4, 2), Left(Tekst27, 2))
tekst29us = DateSerial(Right(Tekst29, 4), Mid(Tekst29, 4, 2), Left(Tekst29, 2))
Do Until rst.EOF = True
If IsNull(rst!Dato) Then
  DatoUs = ""
Else
  'DatoUsTemp = Mid$(rst!Dato, 4, 3) & Left$(rst!Dato, 3) & Right$(rst!Dato, 4)
  'DatoUs = CDate(DatoUsTemp)
  DatoUs = DateSerial(Right(rst!Dato, 4), Mid(rst!Dato, 4, 2), Left(rst!Dato, 2))
End If
If InStr(rst!Medlemsnr, Kombinasjonsboks22) Then
  If DatoUs >= tekst27us And DatoUs <= tekst29us Then
  TextFile.WriteLine (rst!Medlemsnr)
  End If
End If
If InStr(rst!Medlemsnr2, Kombinasjonsboks22) Then
  If DatoUs >= tekst27us And DatoUs <= tekst29us Then
  TextFile.WriteLine (rst!Medlemsnr2)
  End If
End If
 
.... and 26 more of those if's

 

[namliam]

DatoUs = DateSerial(Right(rst!Dato, 4), Mid(rst!Dato, 4, 2), Left(rst!Dato, 2))
.... and 26 more of those if's

[/code]

26 ??? Wow... almost immediatly sounds like you may want to look at your design....


Also looking at your first like DateSerial (Rs!Dato).... you mean to tell me that.... This Dato field is STILL a text field?? NO NO NOOOOOOOOOOOOOOOOOO!

did you read my post#4??

If your field is a date, store it as a frigging date... like conversion functions, field types are there for a reason!

 

[Inkognito]

Actually, No, It is a Medium Date field, so no need for DateSerial there i guess.

But I would still need to change it to US format right?

So how could I do that without loosing the Date value?
First convert it to text and move around the MM and DD, and then CDate again?

 

[namliam]

No such thing as a Medium date field... "Medium date" is a format....
The field type you find next to the name in the table design "Date/Time" or "Text"

If a field is a "Date/Time" field, you DONT need to change it to US format.... it allready is regardsless of how it looks... That is why you DO have to change your user input to US.