Converting calculated queries to an update query

[Tom123456]

Hello,

I've got a select query in which I've created a formula to work out if a 'deferral date' should be provided for an order line.

A: IIf(DateDiff("m",[Instalment Posting Date],[Start date]) Is Null And DateDiff("m",Date(),[Start date])>=1,[Start Date],"")

B: IIf(DateDiff("m",[Instalment Posting Date],[Start date]) Is Not Null And DateDiff("m",[Instalment Posting Date],[Start date])>=1,[Start Date],"")

Deferral Date: IIf([a]="",,[a])

However i cannot 'unshow' A/B in my select query which is not needed nor do any further data changes to the field as the calculation is in the select query rather then the table.

I'd like to change it so that A/B are worked out via update queries on the source table - is this possible? I tried to do it without much success!

Thanks,
Tom

 

[xenou]

You can unshow the "field" by not having it in your query. It's really no different if you had an actual field in the database - you either include it in the query or you do not include it in the query, depending on whether you want to "show" it or not.

 

[james_lankford]

datediff can return null ?

 

[xenou]

I was just checking that and yes, it can, if either of the two dates are null.

 

[xenou]

Well, you don't need an update just to not be able to show or unshow a calculated field (!) but if you still want to pursue an update query for this, I think you'll have to decide what data type the new field is meant to be - which is to say that I really don't like this query because it works with dates and yet can return either a string ("") or a date.

Can you confirm what the data type is for both of these fields:[Instalment Posting Date] and [Start date] -- are they true dates?

If the answer to the above is that you have true dates, your result should also be a date, or null, but not an empty string.

Also where do you expect to have nulls? In Instalment Posting Date, Start Date, or both? Is Start Date never null?

 

[Tom123456]

Hi Xenou,

Thanks for the feedback the datatype for both these fields is Date/Time.

Start data is never null but installment posting date may be.

The new field can be date/time and should return the deferral date currently calculated by the query.

Thanks for your help,

Tom

 

[xenou]

This would be one way (returning null, not empty strings):

update Table1
set	[Deferral Date] = null

update Table1 
set 
	[Deferral Date] = [Start Date] 
where 
	[Instalment Posting Date] is null
	and DateDiff("m",Date(),[Start date])>=1 
	
update Table1 
set 
	[Deferral Date] = [Start Date] 
where 
	[Instalment Posting Date] is not null 
	and [Deferral Date] is null
	and DateDiff("m",[Instalment Posting Date],[Start date])>=1

The logic seems a little messy to me (for instance, deferral date could be null if installment date is null or the comparisons don't pass - you wouldn't necessarily know which though, which seems like a gap in the information provided). I would probably prefer to have two separate reports/queries - one where installment date is null, the other where it is not null, but maybe that's not important here.

You should test carefully. Tally the results with the old formulas, and then with the new formulas.

I think this can be cleaned up and simplified but I'm not sure if I'm right so I'm taking a cautious approach - just reproducing what you have to get the same result.

 

[Tom123456]

Hi Xenou,

Thank you for looking at this - I've implemented & tested it in my database (Ran each update query in turn) and it is working.

I don't need to 'know' the reason deferral data is null or not as long as the logic for determining it is sound - which from my tests it is.

Thank you for your help with this.

Cheers,
Tom

 

[xenou]

Okay, since it's three queries you can either run them in turn, or create a little macro or vba procedure to run them for you (and save a few clicks). You'd need to run these every time you want "up to date" information - otherwise, there could be incorrect results if data were changed or added and the fields were not updated.

 

[Tom123456]

Yes - I've added them to my Macro as there are quite a few update statements and modules etc that need to be called so it's all being lumped into there (in the right order!)