Date Problem in Forms

[Larsporsenna]

I have a very simple problem but can't seem to find a way to achieve it.
In the form "Assets" I want to flag a warning when an asset date is entered that is before the start of the current financial year (01/04/2013)
I tried using a table with date attribute so that the user could change this date as required.
In the Assets form I have an unbound Text field "Financial_Year". In the record source for this field I have the Query "Get Financial Year".
When I run the form #Name? appears in the text field. When I try to force the query with the DoCmd.OpenQuery it opens a data spread sheet.
There must be a simple way to allow me to extract this info and load it into the text Field

 

[Roku]

Please post the SQL for your query "Get Financial Year"

I tried using a table with date attribute so that the user could change this date as required

Which date does this refer to? How does it relate to the financial year?

 

[bob fitz]

Perhaps you could simply set the Validation Rule property for the control, something like:
>=#01/04/2013# And <=#31/03/2014#

 

[Larsporsenna]

SELECT Finance_Year.FinYear
FROM Finance_Year;
There is only ever going to be the one record on the table.

 

[Roku]

Now I'm confused! (more so than usual )

If you only have one record with only one field, why not use a calculated date? Or have a textbox on your form in which the date is entered (maybe with a selection of possible dates a e.g. current financial year plus two previous years)?

Just re-reading your original post, it seems to me that calculating the financial year-end relative to the current year would fit your needs. If you can confirm that, I (or another member) can supply the mechanism.

 

[Larsporsenna]

The reason for the confusion is that I go back many many years and am thinking in terms of having a systems parameter file (and I use the term File because that's how far back I go). It probably would be better to calculate the financial year end relative to the current year. I would be grateful if you could supply the mechanism.

 

[Roku]

this will do the EFY date in VBA.

Public Function getEFY()As Date
getEFY = DateSerial(Year(Now()) - IIf(Month(Now())<4,1,0),4,1)
End Function

you can put this in a Global module or in a Form module as appropriate. Because it is a Public Function, you can use 'getEFY' in a query (using it as a date).

If you don't need the Function, just code the statement in-line wherever you need it (e.g. Form_Open event).

The logic is that the current year is used if the current month is January to March, otherwise the previous year is used. In either case, the month is April and day is 1st.

If you remember parameter files, that suggests mainframe and COBOL? That takes me back to the early 70's when I started as a Programmer-In-Training, commonly known as a PIT. CDC3300 was my mainframe.

 

[Larsporsenna]

That worked fine. Yes you're right I go back to COBOL and punched cards. I even used DIBOL. Anyone remember that? At the minute it's a bit like learning a foreign language, I see a cow and think cow before remembering that the French for cow is vache.

 

[Roku]

Punched cards, paper tape, 7-track magnetic tape, drum disk ... takes me back. One mainframe I worked on had 60KB (yes, KB!) memory (Core variety) and no disk. You really had to know how to use it!

You'd know what 'chad patching' means, I'd guess?

La Vache qui Rit (very cheesy)

 

[Larsporsenna]

When I was at Polytech the ICL 1900 was taken down for (I think it was a week) to upgrade the memory to 128k words. The lecturers assured us that this was going to be a really powerful computer.
As regards chad patching, we called them silver stickies.