Y2k Ensuring a query parameter includes 4 digit year?
Colin Robinson
force/Control date entry with invalid entries not performing the query.
The best i can come up with is
criteria=inputbox("Enter a date with four digit
year","Startdate","dd/mm/yyyy") 'Input mask would be nice
It has the advantage of running whenever the query runs, but lacks
validation. Ideally I want a User defined function with a custom input form.
but how would i cancel the query cleanly if an entry is invalid.
Colin.
Alex Dybenko
inputbox, in dialog mode, with your own function
Best regards,
___________
Alex Dybenko
Point Limited
E-Mail: al...@cemi.rssi.ru
Home Page: http://www.geocities.com/SiliconValley/Heights/5091/
Moscow MS Access User Group
http://www.arimsoft.ru/msaccess
Colin Robinson wrote in message
<6t1dmt$1it$1...@nclient1-gui.server.virgin.net>...
Colin Robinson
>Make your own form, place there a text box with desired mask and open it as
>inputbox, in dialog mode, with your own function
That bit is easy enough it's the clean exit i'm after.
Colin
Alex Dybenko wrote in message ...
Colin Robinson
startdate and enddate ) do not support the format or input mask options to
ensure dates entered are valid dates. The only suggestion here Is that you
display a prompt for the users to enter a four digit date and hope that
they follow it! This can be achieved by entering inputbox ("Enter a
startdate in the format dd/mm/yyyy") in the criteria field.
Creating a user defined function with form, control and inputmask and
executing it instead of inputbox also fails with an error when the query
starts to execute it.
Microsoft have also confirmed this method could not work. the only
suggestion microsoft could offer was to turn the query into a query by form
procedure. adding this is not a y2k issue as the assumption rule would kick
in in this situation.
Shaun Ellis
button, rather than using DoCmd Open Query. But I'm not sure how to
acheive this. Help apreciated.
--
Best regards,
Shaun.
<sha...@dial.pipex.com>
Tim Ferguson
<sens...@innotts.co.uk> wrote:
>How do you
>force/Control date entry with invalid entries not performing the query.
I think it depends on what you mean by "performing" the query.
If this query is a select and you're going to show a datasheet, how
does the user start it up? Select it from the DB window - in which
case you have problems - or clicking a dialog (sorry, Form) or as part
of a VBA routine - when you have complete control over what gets
filled in where.
Same thing applies to action queries: if the user is going to click a
button that says "Click here to delete last year's mistakes" or
something, then you decide exactly how to fill the parameters.
It's most difficult when the query is part of a list box or form
recordset, when sometimes you can't see anyway of controlling what
gets called when. In extremis, you have to leave the .RecordSource
property empty until the Form_Load event.
If you provide a bit more context, then we may be able to give some
more pointed help?
HTH
Tim F
--
<!--- tim...@ferguson.netkonect.co.uk --->
Dev Ashish
Is it an Action query? Then lookup Execute method in Access help.
Both come with examples I think.
HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------
Shaun Ellis wrote in message <35F6E8...@dial.pipex.com>...
:I am trying to run the SQL for a query direct from code behind a form
:
Keri Hardwick
You don't want an inputmask on your form, you want a four digit year
format. Then your user can verify that the default century (if they
enter 2 digit year) is correct, and change it if not.
Keri Hardwick
BG Lover
wrote:
>I am trying to run the SQL for a query direct from code behind a form
>button, rather than using DoCmd Open Query. But I'm not sure how to
>acheive this. Help apreciated.
Basically, you want to issue a DoCmd.RunSQL. The following was taken
from the help file:
DoCmd.RunSQL "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _
"WHERE Employees.Title = 'Sales Manager';"
---
chrisw @ iftech . net
daniel.hedrick
statement then execute it
dim qdf as querydef
dim str as string
str = select <variable containing field> from tablename etc....
qdf.SQL = str
qdf.execute
Shaun Ellis wrote in message <35F6E8...@dial.pipex.com>...
>I am trying to run the SQL for a query direct from code behind a form
>button, rather than using DoCmd Open Query. But I'm not sure how to
>acheive this. Help apreciated.
Tim Ferguson
<daniel....@mci2000.com> wrote:
> str = select <variable containing field> from tablename etc....
^^^^^^
>qdf.SQL = str
>qdf.execute
^^^^^^^
Ummm - you can't execute a select statement. Try either
qdf.sql = "INSERT INTO Bloggs (etc... "
qdf.execute
or
qdf.sql = "SELECT Stuff FROM Somewhere etc... "
Set rs = qdf.OpenRecordset()
The first step is, of course, redundant. If the qdf already exists
(which is a good idea because Jet will optimise a stored qdf but not
an ad-hoc SQL statement), then you don't need to set the .sql
property. If it doesn't exist, then it is simpler to run directly off
the database object:
set rs = CurrentDB.OpenRecordset ("SELECT ..." , dbOpen... , ...)
or
CurrendDB.Execute "INSERT INTO Bloggs..", dbFailOnError, etc..
Hope that is a bit less confusing.
--
<!--- tim...@ferguson.netkonect.co.uk --->
Colin Robinson
Keri Hardwick wrote in message <35F7DA...@sprintmail.com>...
>Colin,
>
>You don't want an inputmask on your form, you want a four digit year
>format. Then your user can verify that the default century (if they
>enter 2 digit year) is correct, and change it if not.
>
format(entry,"dd/mm/yyyy"). I work in an environment where the assumption
rule is not accepted and four digits for the date must be specified only
input masks or verification code or user defined forms can achieve this.
Neither can be applied to a parameter of a query.
Colin.
metra
individually on a form or in input boxes, for example, and concatenate
them. You would need to set up validations for month and day as well as
year, but it should work.
Colin Robinson <sens...@innotts.co.uk> wrote in article
<6tjlpj$bbe$1...@nclient3-gui.server.virgin.net>...
Tim Ferguson
<sens...@innotts.co.uk> wrote:
> I work in an environment where the assumption
>rule is not accepted and four digits for the date must be specified only
>input masks or verification code or user defined forms can achieve this.
Private Sub Text1_LostFocus()
If IsNull(Text1) Then GoTo Text1_LostFocus_Fail
If Not IsDate(Text1) Then GoTo Text1_LostFocus_Fail
'
' This line is the one that does all the work
'
If Format(CDate(Text1), "dd/mm/yyyy") <> Text1.Text Then GoTo
Text1_LostFocus_Fail
Text1_LostFocus_Exit:
Exit Sub
Text1_LostFocus_Fail:
MsgBox "Please put in a proper date in the accepted format", _
vbCritical, _
"Y2K date entry police"
Text1.SetFocus
GoTo Text1_LostFocus_Exit
End Sub
Display formats good; input masks ugh!
colin_r...@my-dejanews.com
want to use query by form I would need to many variations and i have triead
a function() based generic one access can't call the openform once the query
has started.
In article <01bde27d$6de9a520$62adef81@cd7vch2a>,
"metra" <metra.chri...@honeywell.com> wrote:
> You could do it the hard way and get the three parts of the date
> individually on a form or in input boxes, for example, and concatenate
> them. You would need to set up validations for month and day as well as
> year, but it should work.
>
> Colin Robinson <sens...@innotts.co.uk> wrote in article
> <6tjlpj$bbe$1...@nclient3-gui.server.virgin.net>...
> >
> > Keri Hardwick wrote in message <35F7DA...@sprintmail.com>...
> > >Colin,
> > >
> > >You don't want an inputmask on your form, you want a four digit year
> > >format. Then your user can verify that the default century (if they
> > >enter 2 digit year) is correct, and change it if not.
> > >
> > Thanks but ideally I want both, If access assumes the year display it
> > format(entry,"dd/mm/yyyy"). I work in an environment where the
> assumption
> > rule is not accepted and four digits for the date must be specified only
> > input masks or verification code or user defined forms can achieve this.
> > Neither can be applied to a parameter of a query.
> >
> > Colin.
> >
> >
> >
>
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
colin_r...@my-dejanews.com
Yes, I can manage that bit thanks, but it would have to go on a form or
function neither can be called succesfully with the query without an
associated query by form that opens first. or attached to the openevent of a
bound report.
I need something that works with openquery. runsql or run from database query
window?
Colin.
tim...@ferguson.netkonect.co.uk (Tim Ferguson) wrote:
>
> Private Sub Text1_LostFocus()
> If IsNull(Text1) Then GoTo Text1_LostFocus_Fail
> If Not IsDate(Text1) Then GoTo Text1_LostFocus_Fail
> '
> ' This line is the one that does all the work
> '
> If Format(CDate(Text1), "dd/mm/yyyy") <> Text1.Text Then GoTo
> Text1_LostFocus_Fail
>
> Text1_LostFocus_Exit:
> Exit Sub
>
> Text1_LostFocus_Fail:
> MsgBox "Please put in a proper date in the accepted format", _
> vbCritical, _
> "Y2K date entry police"
>
> Text1.SetFocus
> GoTo Text1_LostFocus_Exit
>
> End Sub
>
> Display formats good; input masks ugh!
>
> Tim F
>
> --
> <!--- tim...@ferguson.netkonect.co.uk --->
>
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
George Shears
Access converting the date, have you considered parsing the date string
yourself. Some time ago, when contemplating the Y2k problem, I worked
up some code to turn strings into dates and dates into strings based on
my own rules with the intention of usurping Access default behaviour.
That code follows - it coerces year, month & day ordering based upon
internal constants (not Access/Windows settings) and provides optional
strategies for resolving 2 character years. I have never implemented
this and it needs additional optimization and error checking. You may
be able to modify it to suit your needs.
Option Compare Database 'Use database order for string comparisons
Option Explicit
'---- position of the element in a date string
Const dpos_year = 3
Const dpos_mon = 1
Const dpos_day = 2
'---- individual formatting characters (position above)
Const dfmt_year = "yyyy"
Const dfmt_mon = "mm"
Const dfmt_day = "dd"
Const dfmt_sep = "/"
'---- strategies for handling century when not specified
Const d2ChYr_Current = 0 ' year in current century
Const d2ChYr_Nearest = 1 ' year 50 yrs +/- current (i.e. Financial)
Const d2ChYr_Past = 2 ' year <= current (i.e. Birthdate)
Const d2ChYr_Future = 3 ' year >= current (i.e. Projection)
Const d2ChYr_1900 = 4 ' year in 1900s
'---- actual strategy to be used
Const d2ChYrStrategy = d2ChYr_Nearest
Function date_to_dstr (DateVal As Variant)
Static dstr(3) As String
If IsNull(DateVal) Then
date_to_dstr = Null
Exit Function
ElseIf DateVal < -657434 Or DateVal > 2958465 Then
date_to_dstr = Null
Exit Function
End If
dstr(dpos_year) = Format(DateVal, dfmt_year)
dstr(dpos_mon) = Format(DateVal, dfmt_mon)
dstr(dpos_day) = Format(DateVal, dfmt_day)
date_to_dstr = dstr(1) & dfmt_sep & dstr(2) & dfmt_sep & dstr(3)
End Function
Function dstr_to_date (date_str As String) As Variant
Static dstr(3) As String
Dim dstr_idx As Integer
Dim str_pos As Integer
Dim str_len As Integer
Dim one_ch As String
Dim temp_date As Variant
Dim dtv_day As Integer, dtv_month As Integer, dtv_year As Integer
If IsNull(date_str) Then
dstr_to_date = Null
Exit Function
End If
For dstr_idx = 1 To 3
dstr(dstr_idx) = ""
Next dstr_idx
str_len = Len(date_str)
dstr_idx = 1
str_pos = 0
While (str_pos < str_len) And (dstr_idx <= 3)
str_pos = str_pos + 1
one_ch = Mid(date_str, str_pos, 1)
If (one_ch >= "0") And (one_ch <= "9") Then
dstr(dstr_idx) = dstr(dstr_idx) + one_ch
Else
dstr_idx = dstr_idx + 1
End If
Wend
' ---- initial check - some input
If Len(dstr(dpos_year)) < 2 Or dstr(dpos_mon) = "" Or dstr(dpos_day)
= "" Then
dstr_to_date = Null
Exit Function
End If
' ---- initial check month
dtv_month = Val(dstr(dpos_mon))
If dtv_month < 1 Or dtv_month > 12 Then
dstr_to_date = Null
Exit Function
End If
' ---- initial check day (rough, final performed later)
dtv_day = Val(dstr(dpos_day))
If dtv_day < 1 Or dtv_day > 31 Then
dstr_to_date = Null
Exit Function
End If
' ---- initial rough check year
dtv_year = Val(dstr(dpos_year))
If dtv_year < 0 Then
dstr_to_date = Null
Exit Function
End If
' ---- check if century adjustment required
If dtv_year < 100 Then
' ---- adjust to current century (starting point)
dtv_year = dtv_year + Year(Date) - (Year(Date) Mod 100)
' ---- apply selected strategy to resolve 2 digit years
Select Case d2ChYrStrategy
Case d2ChYr_Current '-- do nothing - have
Case d2ChYr_Past '-- year <= to current
If dtv_year > Year(Date) Then
dtv_year = dtv_year - 100
End If
Case d2ChYr_Future '-- year >= to current
If dtv_year < Year(Date) Then
dtv_year = dtv_year + 100
End If
Case d2ChYr_Nearest '-- year 50 years +/- current
If dtv_year < (Year(Date) - 50) Then
dtv_year = dtv_year + 100
ElseIf dtv_year > (Year(Date) + 50) Then
dtv_year = dtv_year - 100
End If
Case d2ChYr_1900 '-- 1900s
dtv_year = 1900 + (dtv_year Mod 100)
Case Else
MsgBox "Error - Using Current Century for 2 Digit Year."
End Select
End If
' ---- build date value
temp_date = DateSerial(dtv_year, dtv_month, dtv_day)
' ---- final check and return
If Day(temp_date) <> dtv_day Or Month(temp_date) <> dtv_month Then
dstr_to_date = Null
Else
dstr_to_date = temp_date
End If
End Function
Tim Ferguson
wrote:
>I need something that works with openquery. runsql or run from database query
>window?
One thing has been bothering me thoughout this thread, and that is
that I don't really understand the context in which this issue is a
problem.
It seems that you are expecting your users to open the database
container, find a query and either display its datasheet or run an
action. These are users who are not competent to type in a date in an
accepted format. You also mention RunSQL and OpenQuery -- I've just
had to look these up in help and I cannot see what use they are,
having less control than the equivalent Execute and OpenRecordset
methods. If you were to adopt the more flexible VBA approach, then you
would of course have full control over the paramers and could collect
them from the user in any way you choose.
In the end, this question simply rehearses the uselessness of Macros
in a programming environment like Access. It was a nice try by MS to
make DB design into an end-user venture, but every attempt from dBase
IV onwards has proved that it's a no-no. If you want to write a
database, I'm afraid you're going to have to roll up your sleeves and
get into a programming language. (After relational theory, a bit of
structured programming should be a cinch!)
Hope that helps?
Max Hugen
Bit surprised by all the discussion on Y2K.
1. If you specify a format (eg dd/mm/yyyy) at field level, thats what
will show on forms, reports and query results unless you override it.
2. If a field is not specifically formatted on the form, it will display
a 4 digit year even if the user enters 2.
3. If its absolutely essential to force the user into entering 4 digits,
you would use a mask, and perhaps check it using the isdate() function
before using the value.
4. In queries, if you enter a year in the criteria, Access will
abbreviate it (undesirably!) to 2 digits. Workaround: switch to SQL
view, edit the date there, then save and go back to design view. It will
still apppear as 2 digits, but will use the 4 digits as per the SQL
statement.
5. In case you haven't found it, here's how Access arbitrates 2 digit
years:
Date range for abbreviated year Interpretation
1/1/00 through 12/31/29 1/1/2000 through
12/31/2029
1/1/30 through 12/31/99 1/1/1930 through
12/31/1999
6. Even though the query window lets you enter a date in your PCs
format, remember that you must use the US format, ie mm/dd/yyyy, when
you use embedded SQL statements in your code.
I've been 'around the world' with dates, they are a real pain, and every
program treats them differently. Even within some MS products they are
different!
Hope I've helped - if you have a specific problem you're not winning,
email the details if you like and I'll try to assist if I can.
Chers, Max ;-\
--
MAX HUGEN
Hugen Enterprises, 207 Flood St, Leichhardt NSW 2040, Australia
Email: maxh...@ozemail.com.au Tel: +612 9560 3061
....for world-class, custom business software......