2010 dates not showing in access 2003 Query

[KevinSlater]

Hi,

We have a query within a Microsoft Access 2003 database that looks at some data within Oracle tables including a period field and a year field that reference a field in an oracle table named: YEAR_PERIOD. The query has been working fine up until this year. It doesn’t want to display any 2010 dates. I would like the query to be able to display all years including 2010.

I think it could be to do with the functions used in the query as some MID functions have been used and may need to be altered. I have tried to amend but there is still no data showing for 2010 even though data exists in the oracle table for 2010. for example in the YEAR_PERIOD field 1001 exists (for 2010 – Period 1).

I have attached an excel output of the data for 2009 along with the formulas used in the query. Any help would be great.

 

[DCrake]

It could be this that is the route of the problem

YEAR: Val("200"+Mid([YEAR_PERIOD],1,1))

Try changing it to

YEAR: Val("20"+Mid([YEAR_PERIOD],1,2))

also

Left([Year_Period],1) is the same as Mid([YEAR_PERIOD],1,1)

David

 

[KevinSlater]

Hi, ok thanks 2010 dates seem to be showing now but not any years before, any ideas?

 

[DCrake]

Then some how you need an IIF statement to determine which method to adopt. It may be more efficient to write a function to do this. As long as you can write down the logic you can convert it into a function.

David

 

[KevinSlater]

Ok thats sounds like a good option, do you mean something such as IF year is <=2009 then YEAR: Val("200"+Mid([YEAR_PERIOD],1,1)) else YEAR: Val("20"+Mid([YEAR_PERIOD],1,2))

and if period <=2009 Mid([YEAR_PERIOD],1,1) else Mid([YEAR_PERIOD],1,2)


hm not sure how to put this into a working function, any advise would be appreciated.

 

[gemma-the-husky]

that must just be a format thing

taking DC's code snippet

YEAR: Val("20"+Mid([YEAR_PERIOD],1,2))

I suspect a year 2009 is being encoded as

20+9 = 209, not 2009



so maybe

YEAR: Val("20" & Mid([YEAR_PERIOD],1,2)) - & rather than plus

or maybe
YEAR: Val("20"+format(Mid([YEAR_PERIOD],1,2),"00") - coerce the year to 2 characters

 

[KevinSlater]

Ok gemma, i'll try your suggestions now.

 

[DCrake]

Open up an existing or new standard module and place the following function in side it

Public Function GetYear(xYear,xPeriod) As String

If xYear <= 2009 Then
    GetYear = Val("200"+Mid(xYear,1,1))
Else If xYear <= 2009 And xPeriod <=2009 Then
    GetYear = Val("20"+Mid(xYear,1,2))
Else
    'Some code needs to go here not represented in example
End If

End Function

Then in your query or on your form you would use is as follows

Query:

InvYear:GetYear([AnyYear],[AnyPeriod])

Form:

Me.TxtInvYear = GetYear(Me.TxtAnyYear,Me.TxtAnyPeriod)

All code is untested and field names are used for brevity.

David

 

[KevinSlater]

I tried this:
YEAR: Val("20" & Mid([YEAR_PERIOD],1,2))

and i got 2010, but for some of the other years i got some strange dates such as :
2071 - think this is meant to be 2007
2081 - think this is meant to be 2008

 

[DCrake]

If you sample the before and after entities then maybe, just maybe, we can determine exactly what you are attempting to achieve. Don't forget you need to supply past, present and future examples.

David

 

[PeterF]

The original expression would not have shown dates before the milenium.

Because oracle stores the date as a integer it looses the trailing zero.
To avoid this you can format the integer.

YEAR: Val("20"+left(format([YEAR_PERIOD],"0000"),2))

For the same reason you need to change the period.

PERIOD: Val(right([YEAR_PERIOD],2))

 

[KevinSlater]

I can supply more information if you let me know what would help. Do you mean sample screenshots of the query results?, or the SQL code?

 

[gemma-the-husky]

OK

this bit is trying to take 2 characters from your string YEAR_PERIOD, starting at position 1

Mid([YEAR_PERIOD],1,2))

so if this stores 0911 say, then this should snip out the 09 bit.

it is clearly doing something else, so it is getting 71, or 81 - can you see what data is actually stored in this field?

 

[KevinSlater]

Peterf: Your solution worked fine, many thanks for that