Hi,

Would anyone know of a way in which to test if two fields are the same
in a row of data (ie, two dates)? 

Then,

If these two fields are identical I need to delete the contents of two
other fields.

I think I need an IIf statement..............anyone with any
ideas???????????????

very best regards.

Tim

This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.

iif statements are slow particularly when there are lots of rows of data

I suggest a function which is roughly like this

Global function dateCompare (date1, date2) as boolean

dim date1, date2 as date

if date1 = date2 then
	dateCompare = true
else dateCompare = false
end if


end function



If you need to be more sophiticated you can  use the  datediff function to
check which comes first and by how many.

in your query grid you then include

ysnSameDate: dateCompare([dtmDate1],[dtmDate2])



Steve K


-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: 27 February 2003 08:47
To: Access
Subject: [access] IIf statement in Select Query Design Grid


Hi,

Would anyone know of a way in which to test if two fields are the same
in a row of data (ie, two dates)?

Then,

If these two fields are identical I need to delete the contents of two
other fields.

I think I need an IIf statement..............anyone with any
ideas???????????????

very best regards.

Tim

This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed.  It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.

Original message:

Would anyone know of a way in which to test if two fields are the same
in a row of data (ie, two dates)?

Then,

If these two fields are identical I need to delete the contents of two
other fields.

I think I need an IIf statement..............anyone with any
ideas???????????????

Reply:

if you are doing this in some event code (such as the LostFocus or
BeforeUpdate event), just do



if me.DateA = me.DateB then

......

end if



If you are doing this in a function where you are checking against a table
(e.g. processing the table row by row and checking the dates), do



if MyTable!DateA = MyTable!DateB then

.....

end if



You supply the code to respond to the fact that the codes match and change
the table and field names to suit.



Watch out for fields that contain date and time because you may be wanting
to check if the DATE portion of the fields is the same regardless of the
TIME portion.

If this is the case check the integer of the date

e.g. if Int(MyTable!.DateA) = Int(Mytable!DateB) then

....

end if



Does this help?








*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

Tim

I think this will work:

UPDATE Table1 SET Table1.fld1 = Null, Table1.fld2 = Null
WHERE (((Table1.fld3)=[Table1]![fld4]));

Brian

>  from:    Tim Maher <tim.maher@s...>
>  date:    Thu, 27 Feb 2003 08:47:24
>  to:      access@p...
>  subject: Re: [access] IIf statement in Select Query Design Grid
> 
> Hi,
> 
> Would anyone know of a way in which to test if two fields are the same
> in a row of data (ie, two dates)? 
> 
> Then,
> 
> If these two fields are identical I need to delete the contents of two
> other fields.
> 
> I think I need an IIf statement..............anyone with any
> ideas???????????????
> 
> very best regards.
> 
> Tim
> 
> This e-mail transmission is strictly confidential and intended solely 
> for the person or organisation to who it is addressed.  It may contain 
> privileged and confidential information and if you are not the 
> intended recipient, you must not copy, distribute or take any action 
> in reliance on it.  
> If you have received this email in error, please notify us as soon as 
> possible and delete it.
> This e-mail has been scanned using Anti-Virus software, however, 
> Swansea NHS Trust accept no responsibility for infection caused by 
> any virus received on the recipients system.
> 
> 
> 
> 

Thanks for replying

The two fields that Im comparing are both Date/Time fields, HOWEVER,
the first date has dd/mm/yy hh:mm:ss format and the second has dd/mm/yy
format.  my current code looks like:

Set db = CurrentDb
Set rs = db.OpenRecordset("3 YEARS > APR 98")

rs.MoveFirst

Do Until rs.EOF
     If (rs.Fields("DISDATE").value) = (rs.Fields("EPIEND").value)
Then
        rs.Edit
        rs.Fields("equal").value = True
        rs.Update
     Else
        rs.Edit
        rs.Fields("equal").value = False
        rs.Update
     End If
     rs.MoveNext
     
     'to see current row number being tested
     txt1 = counter
     counter = counter + 1
     Me.Repaint
     DoEvents
Loop


Due to the difference in date formats this code doesnt work as it
should on paper!! Any further ideas ?????

Best regards

Tim 
>>> joe.dunn@c... 02/27/03 09:17am >>>

Original message:

Would anyone know of a way in which to test if two fields are the same
in a row of data (ie, two dates)?

Then,

If these two fields are identical I need to delete the contents of two
other fields.

I think I need an IIf statement..............anyone with any
ideas???????????????

Reply:

if you are doing this in some event code (such as the LostFocus or
BeforeUpdate event), just do



if me.DateA = me.DateB then

......

end if



If you are doing this in a function where you are checking against a
table
(e.g. processing the table row by row and checking the dates), do



if MyTable!DateA = MyTable!DateB then

.....

end if



You supply the code to respond to the fact that the codes match and
change
the table and field names to suit.



Watch out for fields that contain date and time because you may be
wanting
to check if the DATE portion of the fields is the same regardless of
the
TIME portion.

If this is the case check the integer of the date

e.g. if Int(MyTable!.DateA) = Int(Mytable!DateB) then

....

end if



Does this help?








*************************************************************************

This e-mail may contain confidential information or be privileged. It
is intended to be read and used only by the named recipient(s). If you
are not the intended recipient(s) please notify us immediately so that
we can make arrangements for its return: you should not disclose the
contents of this e-mail to any other person, or take any copies. Unless
stated otherwise by an authorised individual, nothing contained in this
e-mail is intended to create binding legal obligations between us and
opinions expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by
the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number
3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number
2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales
number 3390839 - for ISAs and investment products bearing the CIS name
Registered offices: Miller Street, Manchester M60 0AL   Telephone 
0161-832-8686   Internet  http://www.cis.co.uk   E-mail cis@c... 

CIS Deposit and Instant Access Savings Accounts are held with The
Co-operative Bank p.l.c., registered in England and Wales number 990937,
P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by
CIS Policyholder Services Limited as agent of the Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************


This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.