Need help with IF() syntax

[miket48]

I am having trouble nailing an IF statement selecting records for a report. The logic in English goes like this. If a license plate is photographed 3 times or more during a period AND it has been photographed and time stamped just once in the past 14 days, it should be selected for printing. If not, not.

My original select statement was this:

(Parking_Vio_Summary_History->Count_License_Plate>=3).AND.(Parking_Violation_History->Timestamp>=DATE()-14)

That was giving me the vehicles that had 3 or more violations but the report would only print the violations that occurred in the past 14 days, not their entire violation history.

So I started playing with the IF function:

IF((Parking_Vio_Summary_History->Count_License_Plate>=3).AND.(Parking_Violation_History->Timestamp>=DATE()-14), Parking_Vio_Summary_History->Count_License_Plate>=3, Parking_Vio_Summary_History->Count_License_Plate>=3.AND.Parking_Violation_History->Timestamp>=DATE()-365)

I think I have the condition fine. I just don�t know how to compose the true result and the false result. Obviously I want the true result to print and the false result to not print.

[MikeC]

Re: Need help with IF() syntax

Mike,
A bit of confusion here...The first part of the condition works fine as you have mentioned, but I am not certain about what you want the criteria to be in the second. As written here...

AND it has been photographed and time stamped just once in the past 14 days,

you would have to have a way to determine how many times it has been time stamped in the last 14 days. If you have such a calculated field, say called No_Timestamps that already had say a dbsum() or tablesum() that showed how many records had a timestamp, then something like--

(Parking_Vio_Summary_History->Count_License_Plate>=3) .AND. (No_Timestamps=1)

If you didn't mean what you said, then try to say it again please...

[miket48]

Re: Need help with IF() syntax

Sorry for the confusion. My error.

I have developed an A5 interface to an Autovu License Plate Recognition System. The statement actually should have said, "If there are one or more records of a violation, as determined by the existence of one or more Timestamps, in the past 14 days." I hope that clarifies.

My original syntax in the select gave me all vehicle plates that had been photographed 3 or more times since the beginning of the system install AND would only select those records that also had a violation (Timestamp) in the past 14 days. Trouble is, on the letter to the violator it would only print records with Timestamps in the past 14 days and not the entire number of Timestamps (violations) since install.

I tried to use the IF() function to get me where I need to be, but not skillful enough yet to pull it off.

Thanks for the help,
Mike

[MikeC]

Re: Need help with IF() syntax

Sorry Mike,

I know I can do this but have a blank right now--could be the late time or my just not feeliing quite right tonight. I will come back to this tomorrow if noone else has come to your (my!) aid by then. A sample would be nice to work on but should actually be fairly straight-forward......

[Pat Bremkamp]

Re: Need help with IF() syntax

In order to print everything if the existance of one or more, then you need to add another if statement:
Something like:

(Parking_Vio_Summary_History->Count_License_Plate>=3).AND. if((Parking_Violation_History->Timestamp>=DATE()-14), .t., .f. )

Pat

[MikeC]

Re: Need help with IF() syntax

Thanks Pat!,

I also think I saw the light as am a little more....well, lets say "alert" today! :)

I came up with this and should work as well--

dim num as n
dim flter as c
flter="eval(between_date(\"Timestamp\",date()-14,date()))"
num=tablecount("Parking_Violation_History",flter)

if(num>=1,(Parking_Vio_Summary_History->Count_License_Plate>=3),.F.)
'or maybe even if(num>=1,(Parking_Vio_Summary_History->Count_License_Plate>=3),[COLOR="Red"]""[/COLOR])

Your filter would then be equal to the if statement.

[miket48]

Re: Need help with IF() syntax

Thanks a bunch.

[miket48]

Re: Need help with IF() syntax

I thought I had enough help and direction to put this as the select record filter and have it work, but it keeps giving me an invalid expression message.

IF(tablecount("Parking_Violation_History",eval(between_date(\ "Parking_Violation
_History->Timestamp\ ",Date()-14,Date())))>=1,Parking_Vio_Summary_History->
Count_License_Plate>=3,.f.)

I saved the last message as both xbasic code and a function, but I don't know how to call it before printing the report.

Thanks,
Mike

[MikeC]

Re: Need help with IF() syntax

Mike,
Present a sample and am sure I or someone can figure out the correct syntax--much easier to do such things hands on---I could guess....and guess....and guess again though-- I guess!!! :)

One more blind attempt by me---I really should work on these types things later on in the am....like after maybe 6 hours of sleep! I may have to use a tablemax, occurs, or something of the like, but will wait for others or a sample before going further.

dim num as n
dim flter as c
flter="eval(between_date(\"Timestamp\",date()-14,date()))"
num=tablecount("Parking_Violation_History",flter)

Your_Filter="num>=1 .AND. (Parking_Vio_Summary_History->Count_License_Plate>=3)"

[miket48]

Re: Need help with IF() syntax

Here is a sample of the database and my report. The more I think about what I am trying to do, apply a filter to do my record selection, but ignore my filter to print all the Timestamps (violations) relative to the records. It may just be impossible to do.

Mike

[MikeC]

Re: Need help with IF() syntax

Mike,
I think I have it but please give me an example of a date period and what specific records should come up--even more than one example would be great--but out of time for a few now, but will check back when I can. I set it up to check back 44 days and got one record for Barnhart circle, 2 records for Orrington circle.........

[miket48]

Re: Need help with IF() syntax

The logic is the same, but you may want to change DATE()-14 to DATE()-30 so you will be sure to grab more records. A quick look at the Violations History table shows that 11 Acero Court looks like a good prospect. They have a car with plate 5LIY827 that first violated on 8/28/08 and last violated on 11/3/08.

If the selection is working properly, license_count>=3 and DATE()-30. should make it qualify. Then look at the letter and see that ALL violations are printed going back to the first on 8/28/08.

Thanks,
Mike

[peteconway]

Re: Need help with IF() syntax

I had all sorts of issues in my dialogs doing this. Here are some commands that I use that may help you.


Peter

dim filename   as c
dim exportfile as c
dim DropDownBox1 as c

'dim Client_Code as c
'dim Date_Type as c
'dim To_Date as d
'dim From_Date as d
'dim Export_Order as c

	exportfile = Client_Code + " Release by" + " Date_Type " + time("dd-MM-yyyy", now(.t.)+ 36000) + ".xls"
	filename = session.session_folder + exportfile
	dim a_tbl as p
	a_tbl = table.open("<ADB_Path>\releases")
	
	
  if Date_Type = "Actual Shipped Date" then

      if Export_Order = "Release" then 
			query.order = "release"
	  end if 

      if Export_Order = "Date" then 
			query.order = "Date_Order_Dispatched"
	  end if 

		query.Filter =  "Client_id= \""+Client_Code + chr(34) + " .and. between(Date_Order_Dispatched,ctod(\""+From_Date+"\"),ctod(\""+To_Date+"\"))"
  end if
  
  
  if Date_Type = "Release Date" then
  	
  	  if Export_Order = "Release" then 
			query.order = "release"
	  end if 

      if Export_Order = "Date" then 
			query.order = "Date_Order_Received"
	  end if 

		query.Filter =  "Client_id= \""+Client_Code + chr(34) + " .and. between(Date_Order_Received,ctod(\""+From_Date+"\"),ctod(\""+To_Date+"\"))"
  end if