Date after 27.02.10 query stops working ??

[fibayne]

Hi
I have a report based on a union query, the date parameters for the report come from a form that opens with command button 'Print Report'. The dates are unbound and have default values of Date() and Date()-90
The report was working fine till this morning ie 01 March 2010, I changed the system date to 27th Feb and the report worked again changing the sys date to any date after 27th Feb and it stops working
Very confused has anyone had the same problem ???

thanks in advance for any help...cheers Fi

 

[gemma-the-husky]

hard without seeing your stuff

but it probably takes 1/3/2010 (UK), and treats it as Jan 3rd 2010 (US)

with Feb dates after 12/2/2010 you cant get this ambiguity, so the dates work as UK dates.

I bet a date like 11/2/2010 (Nov 2nd US) wouldnt work properly either!


===========
note you didnt mention 28/2/2010 (a Sunday) but I expect that would've been OK

 

[fibayne]

Hi Dave
28.02.10 doesnt work either basically if i set the system day to anything after 27.02.10 it doesnt work...the PC is set to UK date and time, any ideas ??...cheers Fi

 

[gemma-the-husky]

i am surprised - but as I say, it may not work on 1/3/2010 - because the query may misinterpret the data as Jan 3rd, instead of 1st March.

you need to show us your code/query

 

[fibayne]

Hi
Hopefully this is legible

SELECT VouchersMain.VNum, VouchersMain.VDate, tblAccTranType.Account,tblAccTranType.CurrentAcct,VoucherDetail.txtNarration,IIf([CurrentAcct]="Debit",[TransactionAmount],0) AS Debit, IIf([CurrentAcct]="Credit",[TransactionAmount],0) AS Credit, [Credit]-[Debit] AS Balance, IIf([CurrentAcct]="Credit"And[UnitPrice]>0,-[TransactionAmount]/[UnitPrice],0) AS CRAddUnits,IIf([CurrentAcct]="Debit"And[UnitPrice]>0,[TransactionAmount]/[UnitPrice],0) AS DRAddUnits,tblApplication.ClientAppID, tblApplication.TitleFALU, tblApplication.ForenameFALU, tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.policyLU,tblContactMain.Address1,tblContactMain.Address2,tblContactMain.Address3,tblContactMain.Address4,tblContactMain.Address5,tblContactMain.PostCode,tblContactMain.CountryIDLU,VouchersMain.ClientID

FROM ((tblContactMain INNER JOIN tblApplication ON tblContactMain.ContactMainID = tblApplication.ClientIDFK) INNER JOIN VouchersMain ON tblContactMain.ContactMainID = VouchersMain.ClientID) INNER JOIN (tblAccTranType INNER JOIN VoucherDetail ON tblAccTranType.AccId = VoucherDetail.AccountId) ON VouchersMain.VNum = VoucherDetail.VNum

WHERE (((VouchersMain.VDate) Between [forms]![frmAccountLedger]![FDate] And [Forms]![FrmAccountLedger]![TDate]) AND ((VouchersMain.ClientID)=[forms]![frmClientPolicyLookUp]![ClientMainID]))

UNION SELECT "" AS VNum, [forms]![frmAccountLedger]![fdATE] AS VD, tblAccTranType.Account, tblAccTranType.CurrentAcct,"Opening Balance Brought forward" AS txtNarration, Sum(IIf([CurrentAcct]="Debit",[TransactionAmount],0)) AS SumOfDebit, Sum(IIf([CurrentAcct]="Credit",[TransactionAmount],0)) AS SumOfCredit, Sum([Credit]-[Debit]) AS Balance, Sum(IIf([CurrentAcct]="Credit"And[UnitPrice]>0,[TransactionAmount]/[UnitPrice],0))AS SumOfCRAddUnits,Sum(IIF([CurrentAcct]="Debit"And[UnitPrice]>0,-[TransactionAmount]/[UnitPrice],0)) AS SumOfDRAddUnits, tblApplication.ClientAppID, tblApplication.TitleFALU, tblApplication.ForenameFALU, tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.policyLU,tblContactMain.Address1,tblContactMain.Address2,tblContactMain.Address3,tblContactMain.Address4,tblContactMain.Address5,tblContactMain.PostCode,tblContactMain.CountryIDLU,VouchersMain.ClientID



FROM ((tblContactMain INNER JOIN tblApplication ON tblContactMain.ContactMainID = tblApplication.ClientIDFK) INNER JOIN VouchersMain ON tblContactMain.ContactMainID = VouchersMain.ClientID) INNER JOIN (tblAccTranType INNER JOIN VoucherDetail ON tblAccTranType.AccId = VoucherDetail.AccountId) ON VouchersMain.VNum = VoucherDetail.VNum

WHERE (((VouchersMain.VDate)<[forms]![frmAccountLedger]![FDate]))

GROUP BY"","",tblAccTranType.Account,tblApplication.TitleFALU,tblApplication.ForenameFALU,tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.policyLU,tblContactMain.Address1,tblContactMain.Address2,tblContactMain.Address3,tblContactMain.Address4,tblContactMain.Address5,tblContactMain.PostCode,tblContactMain.CountryIDLU,VouchersMain.ClientID,
tblApplication.ClientAppID,"Opening Balance Brought forward",tblAccTranType.CurrentAcct
ORDER BY VDate, VNum;

this is the union query behind the report..thanks again for looking..Fi

 

[gemma-the-husky]

too complex to decipher easily

BUT

do the separate bits of the union query work?

does one of them fail?

thats the way to test it really.

 

[fibayne]

thanks Dave will try that and post back

 

[gemma-the-husky]

out of interest, your date filters are

WHERE (((VouchersMain.VDate) Between [forms]![frmAccountLedger]![FDate] And [Forms]![FrmAccountLedger]![TDate])

AND

WHERE (((VouchersMain.VDate)<[forms]![frmAccountLedger]![FDate]))

and I still think it will be something to do with the date formatting/storage. (maybe the tdate bit)

 

[vbaInet]

This might interest you (read the 3rd paragraph):

http://uk.videogames.games.yahoo.com/fe/news/playstation-3-struck-by-bug-0899fe.html

Some may think it's an advert haha!!

 

[fibayne]

Hi vbaInet

interesting article...if you hear anything further and if there is a possibility that it has somehow happened in access please let me now....cheers

Fi

 

[vbaInet]

Will do. Apparently they've advised everyone not to use their consoles online (I was reading in the papers). I will keep you informed of any new developments and if you come across some invaluable information, post on here as well.

What version of Access are you using?

 

[fibayne]

Hi Dave
Picking up on your point about the WHERE clause I have changed the both to read

WHERE (((VouchersMain.VDate) Between [forms]![frmAccountLedger]![FDate] And [Forms]![FrmAccountLedger]![TDate])

Which although not working correctly yet it is getting closer to the correct data on the report....any more pointers would be greatly appreciated

thanks again
Fi

 

[gemma-the-husky]

so if you are getting some data in the date range you expect - but not other items - then maybe its because one of joins is failing ie you have a null somewhere.

do you have each section of this query in a visual query. if so start with it in its simplest form, to make sure it picks up everything it should in the data range - then add the additional tables until you find the culprit.

then just make your union query

select * from unionpart1 union
select * from unionpart2

etc.

 

[fibayne]

Hi Dave

Cant understand why this should have stopped the report working after 27.02.10 ??


The first WHERE selects transactions between the dates to report on the statement, the second narrows it down to only report on those where FDate is greater then VDate

Then once the transactions build up ie there are ones before FDate it creates a balance brought forward figure

(I was helped a lot with this query from the forum previously, and probably dont fully understand how it is working...)

However here is what seems to be working at the moment with the following WHERE clauses -

WHERE (((VouchersMain.VDate) Between [forms]![frmAccountLedger]![FDate] And [Forms]![FrmAccountLedger]![TDate]) AND ((VouchersMain.ClientID)=[forms]![frmClientPolicyLookUp]![ClientMainID]))

AND

WHERE (((VouchersMain.VDate)<[forms]![frmAccountLedger]![FDate])AND ((VouchersMain.ClientID)=[forms]![frmClientPolicyLookUp]![ClientMainID]))


thanks very much for your help and hopefully this will have fixed the problem, again if you an ideas as to what went wrong greatly appreciated
cheers
Fi

 

[gemma-the-husky]

that isnt what a union query does though.

a union query selects items satisfying a query, items satisfying another query, and stacks them to produce a super-query, if you will. You cannot reproduce this effect by normal visual query design, hence it has to be done in SQL.

but you CAN do each bit separately, and THEN combine them.

=========
If you arent clear on what you are seeing, you really need to go back and work through it. Just take the first half of the query (ie down to the first UNION statement), copy it into SQL, and then change to visual design mode.

Now you will be able to play with the query, visually and see what is happening.

You can add/remove tables, change the join types between fields etc, add extra fields etc - and generally check that all the items you expect to see are coming through.

And you will get a far better understanding of complex queries.

 

[fibayne]

thanks Dave for the advice, as I had spoken to soon, it isnt working correctly for those statements that have transactions pre VDate, so taking your advice and breaking the query down to work out where it is failing...cheers Fi

 

[fibayne]

Hi ave

thanks for your advice ...I had spoken to soon as statements with pre VDate transactions arent reporting correctly...so I am taking your advice and working thru what the query is doing step by step...will post back with progress,, thanks again Fi