Conditional Extention of result in Quaries.

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

Guest

Hi,
Good Day,
I will appreciate if some help me to solve my problem. I have two timing
fields. I am calculating difference in third field. And in the same field, I
would like extend my result. With “IIF†function. If result is more than 1
minute then result should be 1 hour and if time crossed from 70 minutes then
result should be 2 hours.

Regards,
 
Keen,
you are going to want to create a function to return your desired result.
Set the fields source to =GetMyDesiredResults() so that it will call this
function.
You will need to change the field names to reflect the real names of the
fields
in your database: Me.txtTimeA.Value & Me.txtTimeB.Value
If you need more help with this let me know.
Take Care & God Bless ~ SPARKER ~

Public Function GetMyDesiredResults() As String
If DateDiff("n", Me.txtTimeA.Value, Me.txtTimeB.Value) >= 70 Then
GetMyDesiredResults = "2 Hour"
ElseIf DateDiff("n", Me.txtTimeA.Value, Me.txtTimeB.Value) >= 1 Then
GetMyDesiredResults = "1 Hour"
Else
GetMyDesiredResults = ""
End If
End Function
___________________________________________________________________
 
Sparker,
Good Day,
Thanks for your assistance but my promlem is still there. I think, due to my
lake of knowledge.
I would like to put as below.
ParkingDuration: DateDiff("n",[ParkingDatetime],[DriveOutDateTime])<70,"1
Hour" & IIf >70&<130,"2 Hour"

Please If you correct it accordingly.

Regards,
 
Keen,
I am not sure if these field names you have given me are the names ot the
TextBox Objects on your Form or if they the names of Fields in your Table
where the Data is stored. If they are Fields on your form: Open your form in
design view and then open it with the visual basic editor (The Forms Module)
and paste this:
_________________________________________________________________________
Public Function GetMyDesiredResults() As String

Dim dteTime1 As Date
Dim dteTime2 As Date
Dim intTimeDiff As Integer

dteTime1 = Me.ParkingDatetime.Value
dteTime2 = Me.DriveOutDateTime.Value
intTimeDiff = DateDiff("n", dteTime1, dteTime2)

If intTimeDiff >= 70 Then
GetMyDesiredResults = "2 Hour"
ElseIf intTimeDiff >= 1 Then
GetMyDesiredResults = "1 Hour"
Else
GetMyDesiredResults = ""
End If

End Function
________________________________________________________________________
If these are not the names of the TextBoxes displaying the data on your form
then we will need to write a SQL Select statement to retrieve the values of
those fields from your data table... Let me know how it goes.
Take Care & God Bless ~ SPARKER ~


Access Keen User said:
Sparker,
Good Day,
Thanks for your assistance but my promlem is still there. I think, due to my
lake of knowledge.
I would like to put as below.
ParkingDuration: DateDiff("n",[ParkingDatetime],[DriveOutDateTime])<70,"1
Hour" & IIf >70&<130,"2 Hour"

Please If you correct it accordingly.

Regards,



--
Access Keen User



sparker said:
Keen,
you are going to want to create a function to return your desired result.
Set the fields source to =GetMyDesiredResults() so that it will call this
function.
You will need to change the field names to reflect the real names of the
fields
in your database: Me.txtTimeA.Value & Me.txtTimeB.Value
If you need more help with this let me know.
Take Care & God Bless ~ SPARKER ~

Public Function GetMyDesiredResults() As String
If DateDiff("n", Me.txtTimeA.Value, Me.txtTimeB.Value) >= 70 Then
GetMyDesiredResults = "2 Hour"
ElseIf DateDiff("n", Me.txtTimeA.Value, Me.txtTimeB.Value) >= 1 Then
GetMyDesiredResults = "1 Hour"
Else
GetMyDesiredResults = ""
End If
End Function
___________________________________________________________________
Click to expand...
Click to expand...
 
Sparker,
Good Day,
Thanks for your detailed reply. I will check and come back to you. But in
the mean time I would like to tell you that I did creat a table to calculte
and genrate an invoice and then quary and from quary to form. And within the
quary I am inserting fields to put formulas to calculate. I do not know, it
is recmended or not. And in form of course Field names are different.

I still thinking will be difficulte for me to formualte as you sent me
examples.

Thanks again.


--
Access Keen User



sparker said:
Keen,
I am not sure if these field names you have given me are the names ot the
TextBox Objects on your Form or if they the names of Fields in your Table
where the Data is stored. If they are Fields on your form: Open your form in
design view and then open it with the visual basic editor (The Forms Module)
and paste this:
_________________________________________________________________________
Public Function GetMyDesiredResults() As String

Dim dteTime1 As Date
Dim dteTime2 As Date
Dim intTimeDiff As Integer

dteTime1 = Me.ParkingDatetime.Value
dteTime2 = Me.DriveOutDateTime.Value
intTimeDiff = DateDiff("n", dteTime1, dteTime2)

If intTimeDiff >= 70 Then
GetMyDesiredResults = "2 Hour"
ElseIf intTimeDiff >= 1 Then
GetMyDesiredResults = "1 Hour"
Else
GetMyDesiredResults = ""
End If

End Function
________________________________________________________________________
If these are not the names of the TextBoxes displaying the data on your form
then we will need to write a SQL Select statement to retrieve the values of
those fields from your data table... Let me know how it goes.
Take Care & God Bless ~ SPARKER ~


Access Keen User said:
Sparker,
Good Day,
Thanks for your assistance but my promlem is still there. I think, due to my
lake of knowledge.
I would like to put as below.
ParkingDuration: DateDiff("n",[ParkingDatetime],[DriveOutDateTime])<70,"1
Hour" & IIf >70&<130,"2 Hour"

Please If you correct it accordingly.

Regards,



--
Access Keen User



sparker said:
Keen,
you are going to want to create a function to return your desired result.
Set the fields source to =GetMyDesiredResults() so that it will call this
function.
You will need to change the field names to reflect the real names of the
fields
in your database: Me.txtTimeA.Value & Me.txtTimeB.Value
If you need more help with this let me know.
Take Care & God Bless ~ SPARKER ~

Public Function GetMyDesiredResults() As String
If DateDiff("n", Me.txtTimeA.Value, Me.txtTimeB.Value) >= 70 Then
GetMyDesiredResults = "2 Hour"
ElseIf DateDiff("n", Me.txtTimeA.Value, Me.txtTimeB.Value) >= 1 Then
GetMyDesiredResults = "1 Hour"
Else
GetMyDesiredResults = ""
End If
End Function
___________________________________________________________________


:

Hi,
Good Day,
I will appreciate if some help me to solve my problem. I have two timing
fields. I am calculating difference in third field. And in the same field, I
would like extend my result. With “IIF†function. If result is more than 1
minute then result should be 1 hour and if time crossed from 70 minutes then
result should be 2 hours.

Regards,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top