Date Validation

[Guest]

I wanted that a user cannnot enter date prior than Current month and last
month. So he cant go beyond 2 months but can enter data for last month and
current month. How should i do this.
Also on similar lines, user can't edit transactions on above priniciple ie
he can edit for current month and last month but not prior to last month.

Regards

 

[Guest]

On the before update of the date field insert the code

If Me.[FieldName] <= DateSerial(year(DateAdd("m", -2, date)),
Month(DateAdd("m", -2, date)) + 1, 0) Or Me.[FieldName] >
DateSerial(year(date), Month(date) + 1, 0) Then
MsgBox "Month can't be smaller then ...."
Cancel = True 'Wont let the user exit the field
End If

 

[Guest]

Thanks!
Also i know how to reference a variable .. Example if i assign it as follow :

Dim aNo as variant
aNo=Dlookup("[No]","Locking")

I can reference aNo instead of -1 in below formula without hassle. So if i
set any no in table, formula would accordingly adjust. But problme is how to
use same effect in query. Like i would define below formula same in criteria
but how to incorpoarate above variable.
Regards

On the before update of the date field insert the code

If Me.[FieldName] <= DateSerial(year(DateAdd("m", -2, date)),
Month(DateAdd("m", -2, date)) + 1, 0) Or Me.[FieldName] >
DateSerial(year(date), Month(date) + 1, 0) Then
MsgBox "Month can't be smaller then ...."
Cancel = True 'Wont let the user exit the field
End If

--
\\// Live Long and Prosper \\//
BS"D

I wanted that a user cannnot enter date prior than Current month and last
month. So he cant go beyond 2 months but can enter data for last month and
current month. How should i do this.
Also on similar lines, user can't edit transactions on above priniciple ie
he can edit for current month and last month but not prior to last month.

Regards

 

[Guest]

But problem is how to use same effect in query. How can i reference aNo in
query??

Thanks!
Also i know how to reference a variable .. Example if i assign it as follow :

Dim aNo as variant
aNo=Dlookup("[No]","Locking")

I can reference aNo instead of -1 in below formula without hassle. So if i
set any no in table, formula would accordingly adjust. But problme is how to
use same effect in query. Like i would define below formula same in criteria
but how to incorpoarate above variable.
Regards

On the before update of the date field insert the code

If Me.[FieldName] <= DateSerial(year(DateAdd("m", -2, date)),
Month(DateAdd("m", -2, date)) + 1, 0) Or Me.[FieldName] >
DateSerial(year(date), Month(date) + 1, 0) Then
MsgBox "Month can't be smaller then ...."
Cancel = True 'Wont let the user exit the field
End If

--
\\// Live Long and Prosper \\//
BS"D

I wanted that a user cannnot enter date prior than Current month and last
month. So he cant go beyond 2 months but can enter data for last month and
current month. How should i do this.
Also on similar lines, user can't edit transactions on above priniciple ie
he can edit for current month and last month but not prior to last month.

Regards

 

[Guest]

I'm not sure how you want to use it in a query, but you can

In SQL
Select Dlookup("[No]","Locking") As aNo, DateField From tableName

In query design add a new field, and write
aNo: Dlookup("[No]","Locking")

So you can refer to ANo in your query
I might be off track, I'll need to know what you are trying to do in the
query.

--
\\// Live Long and Prosper \\//
BS"D

But problem is how to use same effect in query. How can i reference aNo in
query??

Thanks!
Also i know how to reference a variable .. Example if i assign it as follow :

Dim aNo as variant
aNo=Dlookup("[No]","Locking")

I can reference aNo instead of -1 in below formula without hassle. So if i
set any no in table, formula would accordingly adjust. But problme is how to
use same effect in query. Like i would define below formula same in criteria
but how to incorpoarate above variable.
Regards

On the before update of the date field insert the code

If Me.[FieldName] <= DateSerial(year(DateAdd("m", -2, date)),
Month(DateAdd("m", -2, date)) + 1, 0) Or Me.[FieldName] >
DateSerial(year(date), Month(date) + 1, 0) Then
MsgBox "Month can't be smaller then ...."
Cancel = True 'Wont let the user exit the field
End If

--
\\// Live Long and Prosper \\//
BS"D


:

I wanted that a user cannnot enter date prior than Current month and last
month. So he cant go beyond 2 months but can enter data for last month and
current month. How should i do this.
Also on similar lines, user can't edit transactions on above priniciple ie
he can edit for current month and last month but not prior to last month.

Regards

 

[Guest]

Sir, am not clear on how to use below! Pls guide me step by step process.
Am clarifying my problem further:
There's Table "Date Locking" which stores no of months, as locking period.
(Numeric Field)
There's Invoice table. I want only those data from invoice table should be
visible which meets date locking months from Date Locking Table. In this
sitution, user cannot edit any old data but data from those months which
matches with Date Locking Table. When i mention dlookup command in query
design, it does'nt accept and upon running query it paramterise that field eg
Dlookup("[Date Locking"]![Date]","Date Locking") will throw
[Date Locking"]![Date] as user parameter seek.

Pls help

I'm not sure how you want to use it in a query, but you can

In SQL
Select Dlookup("[No]","Locking") As aNo, DateField From tableName

In query design add a new field, and write
aNo: Dlookup("[No]","Locking")

So you can refer to ANo in your query
I might be off track, I'll need to know what you are trying to do in the
query.

--
\\// Live Long and Prosper \\//
BS"D

But problem is how to use same effect in query. How can i reference aNo in
query??

Thanks!
Also i know how to reference a variable .. Example if i assign it as follow :

Dim aNo as variant
aNo=Dlookup("[No]","Locking")

I can reference aNo instead of -1 in below formula without hassle. So if i
set any no in table, formula would accordingly adjust. But problme is how to
use same effect in query. Like i would define below formula same in criteria
but how to incorpoarate above variable.
Regards


:

On the before update of the date field insert the code

If Me.[FieldName] <= DateSerial(year(DateAdd("m", -2, date)),
Month(DateAdd("m", -2, date)) + 1, 0) Or Me.[FieldName] >
DateSerial(year(date), Month(date) + 1, 0) Then
MsgBox "Month can't be smaller then ...."
Cancel = True 'Wont let the user exit the field
End If

--
\\// Live Long and Prosper \\//
BS"D


:

I wanted that a user cannnot enter date prior than Current month and last
month. So he cant go beyond 2 months but can enter data for last month and
current month. How should i do this.
Also on similar lines, user can't edit transactions on above priniciple ie
he can edit for current month and last month but not prior to last month.

Regards

 

[Guest]

Pls read in addition to my below post. Alternatively, if i try to bring table
"Date Locking" in query, i need to link with Invoice table but i dont have
anything to link with". So for me this possible is ruled out..

Sir, am not clear on how to use below! Pls guide me step by step process.
Am clarifying my problem further:
There's Table "Date Locking" which stores no of months, as locking period.
(Numeric Field)
There's Invoice table. I want only those data from invoice table should be
visible which meets date locking months from Date Locking Table. In this
sitution, user cannot edit any old data but data from those months which
matches with Date Locking Table. When i mention dlookup command in query
design, it does'nt accept and upon running query it paramterise that field eg
Dlookup("[Date Locking"]![Date]","Date Locking") will throw
[Date Locking"]![Date] as user parameter seek.

Pls help

I'm not sure how you want to use it in a query, but you can

In SQL
Select Dlookup("[No]","Locking") As aNo, DateField From tableName

In query design add a new field, and write
aNo: Dlookup("[No]","Locking")

So you can refer to ANo in your query
I might be off track, I'll need to know what you are trying to do in the
query.

--
\\// Live Long and Prosper \\//
BS"D

But problem is how to use same effect in query. How can i reference aNo in
query??

:

Thanks!
Also i know how to reference a variable .. Example if i assign it as follow :

Dim aNo as variant
aNo=Dlookup("[No]","Locking")

I can reference aNo instead of -1 in below formula without hassle. So if i
set any no in table, formula would accordingly adjust. But problme is how to
use same effect in query. Like i would define below formula same in criteria
but how to incorpoarate above variable.
Regards


:

On the before update of the date field insert the code

If Me.[FieldName] <= DateSerial(year(DateAdd("m", -2, date)),
Month(DateAdd("m", -2, date)) + 1, 0) Or Me.[FieldName] >
DateSerial(year(date), Month(date) + 1, 0) Then
MsgBox "Month can't be smaller then ...."
Cancel = True 'Wont let the user exit the field
End If

--
\\// Live Long and Prosper \\//
BS"D


:

I wanted that a user cannnot enter date prior than Current month and last
month. So he cant go beyond 2 months but can enter data for last month and
current month. How should i do this.
Also on similar lines, user can't edit transactions on above priniciple ie
he can edit for current month and last month but not prior to last month.

Regards

 

[Guest]

In the criteria for the date field, try

DateAdd("m",Dlookup("[No]","Locking") ,Date()-Day(Date())+1)-1

it should bring all the records that there date is bigger then the end of
the month by the parameter you enter, you might need to play with the
parameter you enter to meet your needs

--
\\// Live Long and Prosper \\//
BS"D

Pls read in addition to my below post. Alternatively, if i try to bring table
"Date Locking" in query, i need to link with Invoice table but i dont have
anything to link with". So for me this possible is ruled out..

Sir, am not clear on how to use below! Pls guide me step by step process.
Am clarifying my problem further:
There's Table "Date Locking" which stores no of months, as locking period.
(Numeric Field)
There's Invoice table. I want only those data from invoice table should be
visible which meets date locking months from Date Locking Table. In this
sitution, user cannot edit any old data but data from those months which
matches with Date Locking Table. When i mention dlookup command in query
design, it does'nt accept and upon running query it paramterise that field eg
Dlookup("[Date Locking"]![Date]","Date Locking") will throw
[Date Locking"]![Date] as user parameter seek.

Pls help

I'm not sure how you want to use it in a query, but you can

In SQL
Select Dlookup("[No]","Locking") As aNo, DateField From tableName

In query design add a new field, and write
aNo: Dlookup("[No]","Locking")

So you can refer to ANo in your query
I might be off track, I'll need to know what you are trying to do in the
query.

--
\\// Live Long and Prosper \\//
BS"D


:

But problem is how to use same effect in query. How can i reference aNo in
query??

:

Thanks!
Also i know how to reference a variable .. Example if i assign it as follow :

Dim aNo as variant
aNo=Dlookup("[No]","Locking")

I can reference aNo instead of -1 in below formula without hassle. So if i
set any no in table, formula would accordingly adjust. But problme is how to
use same effect in query. Like i would define below formula same in criteria
but how to incorpoarate above variable.
Regards


:

On the before update of the date field insert the code

If Me.[FieldName] <= DateSerial(year(DateAdd("m", -2, date)),
Month(DateAdd("m", -2, date)) + 1, 0) Or Me.[FieldName] >
DateSerial(year(date), Month(date) + 1, 0) Then
MsgBox "Month can't be smaller then ...."
Cancel = True 'Wont let the user exit the field
End If

--
\\// Live Long and Prosper \\//
BS"D


:

I wanted that a user cannnot enter date prior than Current month and last
month. So he cant go beyond 2 months but can enter data for last month and
current month. How should i do this.
Also on similar lines, user can't edit transactions on above priniciple ie
he can edit for current month and last month but not prior to last month.

Regards

 

[Guest]

Sir, am not able to use Dlookup in query, as it does;nt accept this and
throws [No]![Locking] as user parameter seek instead of value from date
locking table.. Pls help am literly stuck up. Is there way i can set filter
to above date in form load event so that only selective dates comes..

In the criteria for the date field, try

DateAdd("m",Dlookup("[No]","Locking") ,Date()-Day(Date())+1)-1

it should bring all the records that there date is bigger then the end of
the month by the parameter you enter, you might need to play with the
parameter you enter to meet your needs

--
\\// Live Long and Prosper \\//
BS"D

Pls read in addition to my below post. Alternatively, if i try to bring table
"Date Locking" in query, i need to link with Invoice table but i dont have
anything to link with". So for me this possible is ruled out..

Sir, am not clear on how to use below! Pls guide me step by step process.
Am clarifying my problem further:
There's Table "Date Locking" which stores no of months, as locking period.
(Numeric Field)
There's Invoice table. I want only those data from invoice table should be
visible which meets date locking months from Date Locking Table. In this
sitution, user cannot edit any old data but data from those months which
matches with Date Locking Table. When i mention dlookup command in query
design, it does'nt accept and upon running query it paramterise that field eg
Dlookup("[Date Locking"]![Date]","Date Locking") will throw
[Date Locking"]![Date] as user parameter seek.

Pls help

:

I'm not sure how you want to use it in a query, but you can

In SQL
Select Dlookup("[No]","Locking") As aNo, DateField From tableName

In query design add a new field, and write
aNo: Dlookup("[No]","Locking")

So you can refer to ANo in your query
I might be off track, I'll need to know what you are trying to do in the
query.

--
\\// Live Long and Prosper \\//
BS"D


:

But problem is how to use same effect in query. How can i reference aNo in
query??

:

Thanks!
Also i know how to reference a variable .. Example if i assign it as follow :

Dim aNo as variant
aNo=Dlookup("[No]","Locking")

I can reference aNo instead of -1 in below formula without hassle. So if i
set any no in table, formula would accordingly adjust. But problme is how to
use same effect in query. Like i would define below formula same in criteria
but how to incorpoarate above variable.
Regards


:

On the before update of the date field insert the code

If Me.[FieldName] <= DateSerial(year(DateAdd("m", -2, date)),
Month(DateAdd("m", -2, date)) + 1, 0) Or Me.[FieldName] >
DateSerial(year(date), Month(date) + 1, 0) Then
MsgBox "Month can't be smaller then ...."
Cancel = True 'Wont let the user exit the field
End If

--
\\// Live Long and Prosper \\//
BS"D


:

I wanted that a user cannnot enter date prior than Current month and last
month. So he cant go beyond 2 months but can enter data for last month and
current month. How should i do this.
Also on similar lines, user can't edit transactions on above priniciple ie
he can edit for current month and last month but not prior to last month.

Regards