AutoNumber

[Guest]

Hi All,

When I set a field type auto number it generates incremental number every
time when add a new record but when I delete a record it skips the deleted
record number & jumps to next number e.g. I enter record number 1 2 3 ... 10
if I delete number 10 record the next record I enter will be 11 but I want it
should be 10. Please help me.

 

[Guest]

Thanx for your reply, I have one more question for you, I have a bank Account
Management DB, when user try to look their bank statement a pop up form
appears and ask for "start date", "end date" & "Bank Name" if user give all
the details required, report would be shown otherwise not but a problem
occures when it lefts any field blank report not shows any data which I think
wrong, I want if user left empty the "Start Date" and "End Date" fields blank
reprot shows all data of all dates

Abdul,
Such is the nature of Autonumbers. Access always maintains the last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a numeric field
for your table (ex. RecID), and... on your data entry form, make the default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table, the next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hi All,

When I set a field type auto number it generates incremental number every
time when add a new record but when I delete a record it skips the deleted
record number & jumps to next number e.g. I enter record number 1 2 3 ...
10
if I delete number 10 record the next record I enter will be 11 but I want
it
should be 10. Please help me.

 

[Anthos]

use the NZ(function)

When they leave the fields blank, set the default date to be something
really early
( I generally use 1/1/2000, however, it depends on your data)

e.g.

Nz([field], #1/1/2000#)

In the where clause you can put
Where [date] >= Nz([startdate], #1/1/2000#) and [date] <=
Nz([enddate], #31/12/2099#)

Hope that helps,

Kind Regards
Anthony Moore
IT Excellence

 

[John W. Vinson]

Hi All,

When I set a field type auto number it generates incremental number every
time when add a new record but when I delete a record it skips the deleted
record number & jumps to next number e.g. I enter record number 1 2 3 ... 10
if I delete number 10 record the next record I enter will be 11 but I want it
should be 10. Please help me.

Think about this for a minute.

Suppose you have records 1 through 82251.

You discover that record 2 was a dummy test record which should have never
been left in the table.

Do you *really* want to renumber records 3 through 82251, in this table, in
all related tables, on all the printouts that have ever been generated, in
every mind that has remembered any one of these numbers?

A preferable alternative might be to have a Yes/No field [Active] in each
record; rather than deleting the record, just mark it inactive.

John W. Vinson [MVP]

 

[Anthos]

Not to mention the integrity nightmare in this situation,
You would then have to update any reliant tables as well with the new
value.

If this was used only for new records though, it would be an
acceptable solution.

 

[Al Campagna]

Abdul,
Such is the nature of Autonumbers. Access always maintains the last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a numeric field
for your table (ex. RecID), and... on your data entry form, make the default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table, the next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Al Campagna]

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between year 1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanx for your reply, I have one more question for you, I have a bank
Account
Management DB, when user try to look their bank statement a pop up form
appears and ask for "start date", "end date" & "Bank Name" if user give
all
the details required, report would be shown otherwise not but a problem
occures when it lefts any field blank report not shows any data which I
think
wrong, I want if user left empty the "Start Date" and "End Date" fields
blank
reprot shows all data of all dates

Abdul,
Such is the nature of Autonumbers. Access always maintains the last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a numeric field
for your table (ex. RecID), and... on your data entry form, make the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table, the next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Hi All,

When I set a field type auto number it generates incremental number
every
time when add a new record but when I delete a record it skips the
deleted
record number & jumps to next number e.g. I enter record number 1 2 3
...
10
if I delete number 10 record the next record I enter will be 11 but I
want
it
should be 10. Please help me.

 

[Guest]

Thanks Campagna for your reply your code works really well but some errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#) And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me in this
metter.

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between year 1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanx for your reply, I have one more question for you, I have a bank
Account
Management DB, when user try to look their bank statement a pop up form
appears and ask for "start date", "end date" & "Bank Name" if user give
all
the details required, report would be shown otherwise not but a problem
occures when it lefts any field blank report not shows any data which I
think
wrong, I want if user left empty the "Start Date" and "End Date" fields
blank
reprot shows all data of all dates

Abdul,
Such is the nature of Autonumbers. Access always maintains the last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a numeric field
for your table (ex. RecID), and... on your data entry form, make the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table, the next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Hi All,

When I set a field type auto number it generates incremental number
every
time when add a new record but when I delete a record it skips the
deleted
record number & jumps to next number e.g. I enter record number 1 2 3
...
10
if I delete number 10 record the next record I enter will be 11 but I
want
it
should be 10. Please help me.

 

[Al Campagna]

Abdul Shakeel,
I don't understand your error

"Too Many Comples Expression"

What is "Comples?"

And, are you saying that the code I suggested works sometimes and not
other times?

Try...

= NZ([Forms]![Search Form]![txtStartDate], #1/1/1900#)

And <= NZ([Forms]![Search Form]![txtEndDate], #12/31/2099#)

Just another way to phrase it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks Campagna for your reply your code works really well but some errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#) And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me in this
metter.

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between year
1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Thanx for your reply, I have one more question for you, I have a bank
Account
Management DB, when user try to look their bank statement a pop up form
appears and ask for "start date", "end date" & "Bank Name" if user give
all
the details required, report would be shown otherwise not but a problem
occures when it lefts any field blank report not shows any data which I
think
wrong, I want if user left empty the "Start Date" and "End Date" fields
blank
reprot shows all data of all dates

:

Abdul,
Such is the nature of Autonumbers. Access always maintains the
last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a numeric
field
for your table (ex. RecID), and... on your data entry form, make the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the
table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table, the
next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

message
Hi All,

When I set a field type auto number it generates incremental number
every
time when add a new record but when I delete a record it skips the
deleted
record number & jumps to next number e.g. I enter record number 1 2
3
...
10
if I delete number 10 record the next record I enter will be 11 but
I
want
it
should be 10. Please help me.

 

[Guest]

Dear Al Campagna,

your given code is also not working error occures.

This expression is typed incorrectly, or it is too complex to be evaluated.
for example, a numeric expression may contain too many complicated elements,
try simplifying the expression by assigning parts of expression to variables."

Abdul Shakeel,
I don't understand your error

"Too Many Comples Expression"

What is "Comples?"

And, are you saying that the code I suggested works sometimes and not
other times?

Try...

= NZ([Forms]![Search Form]![txtStartDate], #1/1/1900#)

And <= NZ([Forms]![Search Form]![txtEndDate], #12/31/2099#)

Just another way to phrase it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks Campagna for your reply your code works really well but some errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#) And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me in this
metter.

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between year
1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Thanx for your reply, I have one more question for you, I have a bank
Account
Management DB, when user try to look their bank statement a pop up form
appears and ask for "start date", "end date" & "Bank Name" if user give
all
the details required, report would be shown otherwise not but a problem
occures when it lefts any field blank report not shows any data which I
think
wrong, I want if user left empty the "Start Date" and "End Date" fields
blank
reprot shows all data of all dates

:

Abdul,
Such is the nature of Autonumbers. Access always maintains the
last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a numeric
field
for your table (ex. RecID), and... on your data entry form, make the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the
table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table, the
next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

message
Hi All,

When I set a field type auto number it generates incremental number
every
time when add a new record but when I delete a record it skips the
deleted
record number & jumps to next number e.g. I enter record number 1 2
3
...
10
if I delete number 10 record the next record I enter will be 11 but
I
want
it
should be 10. Please help me.

 

[Al Campagna]

Abdul,
Whenever you have trouble with code that has been suggested as a fix,
please cut & paste the code "exactly" into your reply.
We need to see that you implemented that code properly.

I can find nothing wrong with my code, and I've also included other
solutions previously posted in the Access newsgroups.
They each use the methods I suggested.
-----------------------------
Subject: Re: Show all dates if user does not enter a date range

Anyone know how to get around this for dates?? Try
= NZ([Forms]![Search Form]![txtStartDate], #1/1/100#)

and <= NZ([Forms]![Search Form]![txtEndDate], #12/31/9999#)
------------------------------
Subject: Re: Desperate for Criteria Help!
Try this as your criteria:
(This method uses an input box to prompt for the 2 dates... replace the
Input prompts with the location on your date range form and fields)

Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)

If the user enters nothing for the start date, the query will return all
dates
between 1-1-1900 and the [Enter End Date].
If the user enters nothing for the end date, the query will return all
dates
between the [Enter Start Date] and 12-31-2999.
If the user leaves both input fields blank, the query will return all
dates
between 1-1-1900 and 12-31-2999.
------------------------------

As you can see, both methods I suggested should work.

Copy your code exactly in your reply, and describe in detail where you
used it.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Dear Al Campagna,

your given code is also not working error occures.

This expression is typed incorrectly, or it is too complex to be
evaluated.
for example, a numeric expression may contain too many complicated
elements,
try simplifying the expression by assigning parts of expression to
variables."

Abdul Shakeel,
I don't understand your error

"Too Many Comples Expression"

What is "Comples?"

And, are you saying that the code I suggested works sometimes and not
other times?

Try...

= NZ([Forms]![Search Form]![txtStartDate], #1/1/1900#)

And <= NZ([Forms]![Search Form]![txtEndDate], #12/31/2099#)

Just another way to phrase it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Thanks Campagna for your reply your code works really well but some
errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#) And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me in
this
metter.

:

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between year
1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


message
Thanx for your reply, I have one more question for you, I have a
bank
Account
Management DB, when user try to look their bank statement a pop up
form
appears and ask for "start date", "end date" & "Bank Name" if user
give
all
the details required, report would be shown otherwise not but a
problem
occures when it lefts any field blank report not shows any data
which I
think
wrong, I want if user left empty the "Start Date" and "End Date"
fields
blank
reprot shows all data of all dates

:

Abdul,
Such is the nature of Autonumbers. Access always maintains the
last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a numeric
field
for your table (ex. RecID), and... on your data entry form, make
the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the
table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table,
the
next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

message
Hi All,

When I set a field type auto number it generates incremental
number
every
time when add a new record but when I delete a record it skips
the
deleted
record number & jumps to next number e.g. I enter record number 1
2
3
...
10
if I delete number 10 record the next record I enter will be 11
but
I
want
it
should be 10. Please help me.

 

[Anthos]

I have seen the "To Complex to be evaluated" error when trying to do
the following.
a) when you attempt to put Date Limiting functions on a field that
isn't a date.
b) when you try to convert a field to a date, that is really a string
etc.
c) when you have are trying to establish totals in this query, create
a sub query to limit the data, and another to do the total arrithmatic

 

[Guest]

Dear Campagna

I am really very sorry, bcz I am using your code with combo box, cause for
using the combo box for select date criteria is that, I am using a pop up
calendar with its "On Mouse over" & "On Mouse Down" Event but when I Change
these combo Boxes with Text Boxes your code working Properly.....Let me
apologies for this.

Abdul,
Whenever you have trouble with code that has been suggested as a fix,
please cut & paste the code "exactly" into your reply.
We need to see that you implemented that code properly.

I can find nothing wrong with my code, and I've also included other
solutions previously posted in the Access newsgroups.
They each use the methods I suggested.
-----------------------------
Subject: Re: Show all dates if user does not enter a date range

Anyone know how to get around this for dates?? Try
= NZ([Forms]![Search Form]![txtStartDate], #1/1/100#)

and <= NZ([Forms]![Search Form]![txtEndDate], #12/31/9999#)
------------------------------
Subject: Re: Desperate for Criteria Help!
Try this as your criteria:
(This method uses an input box to prompt for the 2 dates... replace the
Input prompts with the location on your date range form and fields)

Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)

If the user enters nothing for the start date, the query will return all
dates
between 1-1-1900 and the [Enter End Date].
If the user enters nothing for the end date, the query will return all
dates
between the [Enter Start Date] and 12-31-2999.
If the user leaves both input fields blank, the query will return all
dates
between 1-1-1900 and 12-31-2999.
------------------------------

As you can see, both methods I suggested should work.

Copy your code exactly in your reply, and describe in detail where you
used it.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Dear Al Campagna,

your given code is also not working error occures.

This expression is typed incorrectly, or it is too complex to be
evaluated.
for example, a numeric expression may contain too many complicated
elements,
try simplifying the expression by assigning parts of expression to
variables."

Abdul Shakeel,
I don't understand your error
"Too Many Comples Expression"
What is "Comples?"

And, are you saying that the code I suggested works sometimes and not
other times?

Try...
= NZ([Forms]![Search Form]![txtStartDate], #1/1/1900#)
And <= NZ([Forms]![Search Form]![txtEndDate], #12/31/2099#)

Just another way to phrase it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Thanks Campagna for your reply your code works really well but some
errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#) And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me in
this
metter.

:

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between year
1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


message
Thanx for your reply, I have one more question for you, I have a
bank
Account
Management DB, when user try to look their bank statement a pop up
form
appears and ask for "start date", "end date" & "Bank Name" if user
give
all
the details required, report would be shown otherwise not but a
problem
occures when it lefts any field blank report not shows any data
which I
think
wrong, I want if user left empty the "Start Date" and "End Date"
fields
blank
reprot shows all data of all dates

:

Abdul,
Such is the nature of Autonumbers. Access always maintains the
last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a numeric
field
for your table (ex. RecID), and... on your data entry form, make
the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the
table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table,
the
next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

message
Hi All,

When I set a field type auto number it generates incremental
number
every
time when add a new record but when I delete a record it skips
the
deleted
record number & jumps to next number e.g. I enter record number 1
2
3
...
10
if I delete number 10 record the next record I enter will be 11
but
I
want
it
should be 10. Please help me.

 

[Al Campagna]

No Problem... glad you found the problem.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Dear Campagna

I am really very sorry, bcz I am using your code with combo box, cause for
using the combo box for select date criteria is that, I am using a pop up
calendar with its "On Mouse over" & "On Mouse Down" Event but when I
Change
these combo Boxes with Text Boxes your code working Properly.....Let me
apologies for this.

Abdul,
Whenever you have trouble with code that has been suggested as a fix,
please cut & paste the code "exactly" into your reply.
We need to see that you implemented that code properly.

I can find nothing wrong with my code, and I've also included other
solutions previously posted in the Access newsgroups.
They each use the methods I suggested.
-----------------------------
Subject: Re: Show all dates if user does not enter a date range

Anyone know how to get around this for dates?? Try
= NZ([Forms]![Search Form]![txtStartDate], #1/1/100#)

and <= NZ([Forms]![Search Form]![txtEndDate], #12/31/9999#)
------------------------------
Subject: Re: Desperate for Criteria Help!
Try this as your criteria:
(This method uses an input box to prompt for the 2 dates... replace
the
Input prompts with the location on your date range form and fields)

Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)

If the user enters nothing for the start date, the query will return
all
dates
between 1-1-1900 and the [Enter End Date].
If the user enters nothing for the end date, the query will return
all
dates
between the [Enter Start Date] and 12-31-2999.
If the user leaves both input fields blank, the query will return all
dates
between 1-1-1900 and 12-31-2999.
------------------------------

As you can see, both methods I suggested should work.

Copy your code exactly in your reply, and describe in detail where
you
used it.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Dear Al Campagna,

your given code is also not working error occures.

This expression is typed incorrectly, or it is too complex to be
evaluated.
for example, a numeric expression may contain too many complicated
elements,
try simplifying the expression by assigning parts of expression to
variables."
:

Abdul Shakeel,
I don't understand your error
"Too Many Comples Expression"
What is "Comples?"

And, are you saying that the code I suggested works sometimes and
not
other times?

Try...
= NZ([Forms]![Search Form]![txtStartDate], #1/1/1900#)
And <= NZ([Forms]![Search Form]![txtEndDate], #12/31/2099#)

Just another way to phrase it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


message
Thanks Campagna for your reply your code works really well but some
errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#) And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me in
this
metter.

:

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between
year
1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


message
Thanx for your reply, I have one more question for you, I have a
bank
Account
Management DB, when user try to look their bank statement a pop
up
form
appears and ask for "start date", "end date" & "Bank Name" if
user
give
all
the details required, report would be shown otherwise not but a
problem
occures when it lefts any field blank report not shows any data
which I
think
wrong, I want if user left empty the "Start Date" and "End Date"
fields
blank
reprot shows all data of all dates

:

Abdul,
Such is the nature of Autonumbers. Access always maintains
the
last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a
numeric
field
for your table (ex. RecID), and... on your data entry form, make
the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the
table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table,
the
next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

in
message
Hi All,

When I set a field type auto number it generates incremental
number
every
time when add a new record but when I delete a record it skips
the
deleted
record number & jumps to next number e.g. I enter record
number 1
2
3
...
10
if I delete number 10 record the next record I enter will be
11
but
I
want
it
should be 10. Please help me.

 

[Guest]

Campagna,

I have a report whcih contains customer Names, on report Header and details
Like Date, order number, units, rate, qty etc. in detail section when I open
the report a popup form appears and ask for StartDate, EndDate & have a list
contains all customers names to be enter, I want that in me customer List if
I enable MultiSelect to Expanded & then I Select more then one customer
report should show order detail of all selected customers. so what would be
the parameter query in this situation.

No Problem... glad you found the problem.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Dear Campagna

I am really very sorry, bcz I am using your code with combo box, cause for
using the combo box for select date criteria is that, I am using a pop up
calendar with its "On Mouse over" & "On Mouse Down" Event but when I
Change
these combo Boxes with Text Boxes your code working Properly.....Let me
apologies for this.

Abdul,
Whenever you have trouble with code that has been suggested as a fix,
please cut & paste the code "exactly" into your reply.
We need to see that you implemented that code properly.

I can find nothing wrong with my code, and I've also included other
solutions previously posted in the Access newsgroups.
They each use the methods I suggested.
-----------------------------
Subject: Re: Show all dates if user does not enter a date range
Anyone know how to get around this for dates??
Try
= NZ([Forms]![Search Form]![txtStartDate], #1/1/100#)
and <= NZ([Forms]![Search Form]![txtEndDate], #12/31/9999#)
------------------------------
Subject: Re: Desperate for Criteria Help!
Try this as your criteria:
(This method uses an input box to prompt for the 2 dates... replace
the
Input prompts with the location on your date range form and fields)

Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)

If the user enters nothing for the start date, the query will return
all
dates
between 1-1-1900 and the [Enter End Date].
If the user enters nothing for the end date, the query will return
all
dates
between the [Enter Start Date] and 12-31-2999.
If the user leaves both input fields blank, the query will return all
dates
between 1-1-1900 and 12-31-2999.
------------------------------

As you can see, both methods I suggested should work.

Copy your code exactly in your reply, and describe in detail where
you
used it.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Dear Al Campagna,

your given code is also not working error occures.

This expression is typed incorrectly, or it is too complex to be
evaluated.
for example, a numeric expression may contain too many complicated
elements,
try simplifying the expression by assigning parts of expression to
variables."
:

Abdul Shakeel,
I don't understand your error
"Too Many Comples Expression"
What is "Comples?"

And, are you saying that the code I suggested works sometimes and
not
other times?

Try...
= NZ([Forms]![Search Form]![txtStartDate], #1/1/1900#)
And <= NZ([Forms]![Search Form]![txtEndDate], #12/31/2099#)

Just another way to phrase it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


message
Thanks Campagna for your reply your code works really well but some
errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#) And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me in
this
metter.

:

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between
year
1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


message
Thanx for your reply, I have one more question for you, I have a
bank
Account
Management DB, when user try to look their bank statement a pop
up
form
appears and ask for "start date", "end date" & "Bank Name" if
user
give
all
the details required, report would be shown otherwise not but a
problem
occures when it lefts any field blank report not shows any data
which I
think
wrong, I want if user left empty the "Start Date" and "End Date"
fields
blank
reprot shows all data of all dates

:

Abdul,
Such is the nature of Autonumbers. Access always maintains
the
last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a
numeric
field
for your table (ex. RecID), and... on your data entry form, make
the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the
table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table,
the
next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

in
message
Hi All,

When I set a field type auto number it generates incremental
number
every
time when add a new record but when I delete a record it skips
the
deleted
record number & jumps to next number e.g. I enter record
number 1
2
3
...
10
if I delete number 10 record the next record I enter will be
11
but
I
want
it
should be 10. Please help me.

 

[Guest]

Hi All,

I have customer Order Form, Includes General Customer Information & Order
Details on the Sub Form Sub form contains Prduct Name, Unit, Rate I want that
when I am on Product Field by pressing a shortkey Like Ctrl+D a popup form
appear and ask for character or Keywords to be enter for searching a
particuler product e.g. I have apple as product the Keyword "app" appears it
in product field or a popup form appear contains list of all product when I
double click on a particuler product it appears on Product field.

Thanks.

No Problem... glad you found the problem.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Dear Campagna

I am really very sorry, bcz I am using your code with combo box, cause for
using the combo box for select date criteria is that, I am using a pop up
calendar with its "On Mouse over" & "On Mouse Down" Event but when I
Change
these combo Boxes with Text Boxes your code working Properly.....Let me
apologies for this.

Abdul,
Whenever you have trouble with code that has been suggested as a fix,
please cut & paste the code "exactly" into your reply.
We need to see that you implemented that code properly.

I can find nothing wrong with my code, and I've also included other
solutions previously posted in the Access newsgroups.
They each use the methods I suggested.
-----------------------------
Subject: Re: Show all dates if user does not enter a date range
Anyone know how to get around this for dates??
Try
= NZ([Forms]![Search Form]![txtStartDate], #1/1/100#)
and <= NZ([Forms]![Search Form]![txtEndDate], #12/31/9999#)
------------------------------
Subject: Re: Desperate for Criteria Help!
Try this as your criteria:
(This method uses an input box to prompt for the 2 dates... replace
the
Input prompts with the location on your date range form and fields)

Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)

If the user enters nothing for the start date, the query will return
all
dates
between 1-1-1900 and the [Enter End Date].
If the user enters nothing for the end date, the query will return
all
dates
between the [Enter Start Date] and 12-31-2999.
If the user leaves both input fields blank, the query will return all
dates
between 1-1-1900 and 12-31-2999.
------------------------------

As you can see, both methods I suggested should work.

Copy your code exactly in your reply, and describe in detail where
you
used it.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Dear Al Campagna,

your given code is also not working error occures.

This expression is typed incorrectly, or it is too complex to be
evaluated.
for example, a numeric expression may contain too many complicated
elements,
try simplifying the expression by assigning parts of expression to
variables."
:

Abdul Shakeel,
I don't understand your error
"Too Many Comples Expression"
What is "Comples?"

And, are you saying that the code I suggested works sometimes and
not
other times?

Try...
= NZ([Forms]![Search Form]![txtStartDate], #1/1/1900#)
And <= NZ([Forms]![Search Form]![txtEndDate], #12/31/2099#)

Just another way to phrase it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


message
Thanks Campagna for your reply your code works really well but some
errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#) And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me in
this
metter.

:

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)
If your date fields are null, all records with a date between
year
1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


message
Thanx for your reply, I have one more question for you, I have a
bank
Account
Management DB, when user try to look their bank statement a pop
up
form
appears and ask for "start date", "end date" & "Bank Name" if
user
give
all
the details required, report would be shown otherwise not but a
problem
occures when it lefts any field blank report not shows any data
which I
think
wrong, I want if user left empty the "Start Date" and "End Date"
fields
blank
reprot shows all data of all dates

:

Abdul,
Such is the nature of Autonumbers. Access always maintains
the
last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a
numeric
field
for your table (ex. RecID), and... on your data entry form, make
the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine the
table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the table,
the
next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

in
message
Hi All,

When I set a field type auto number it generates incremental
number
every
time when add a new record but when I delete a record it skips
the
deleted
record number & jumps to next number e.g. I enter record
number 1
2
3
...
10
if I delete number 10 record the next record I enter will be
11
but
I
want
it
should be 10. Please help me.

 

[Al Campagna]

Abdul,
It's not considered good practice to address new problems, in the
same thread as a previously "solved" problem.

First, probably only I will see it, rather than the whole
community of responders.
Secondly, responders are not likely to bore down through a
long "answered" thread to see if there's a new "unanswered" issue.

Please create an entriely new post for this problem... and I'm sure
someone will pick it up.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Campagna,

I have a report whcih contains customer Names, on report Header and
details
Like Date, order number, units, rate, qty etc. in detail section when I
open
the report a popup form appears and ask for StartDate, EndDate & have a
list
contains all customers names to be enter, I want that in me customer List
if
I enable MultiSelect to Expanded & then I Select more then one customer
report should show order detail of all selected customers. so what would
be
the parameter query in this situation.

No Problem... glad you found the problem.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Dear Campagna

I am really very sorry, bcz I am using your code with combo box, cause
for
using the combo box for select date criteria is that, I am using a pop
up
calendar with its "On Mouse over" & "On Mouse Down" Event but when I
Change
these combo Boxes with Text Boxes your code working Properly.....Let me
apologies for this.

:

Abdul,
Whenever you have trouble with code that has been suggested as a
fix,
please cut & paste the code "exactly" into your reply.
We need to see that you implemented that code properly.

I can find nothing wrong with my code, and I've also included other
solutions previously posted in the Access newsgroups.
They each use the methods I suggested.
-----------------------------
Subject: Re: Show all dates if user does not enter a date range
Anyone know how to get around this for dates??
Try
= NZ([Forms]![Search Form]![txtStartDate], #1/1/100#)
and <= NZ([Forms]![Search Form]![txtEndDate], #12/31/9999#)
------------------------------
Subject: Re: Desperate for Criteria Help!
Try this as your criteria:
(This method uses an input box to prompt for the 2 dates...
replace
the
Input prompts with the location on your date range form and fields)

Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter End
Date],#12/31/2999#)

If the user enters nothing for the start date, the query will
return
all
dates
between 1-1-1900 and the [Enter End Date].
If the user enters nothing for the end date, the query will return
all
dates
between the [Enter Start Date] and 12-31-2999.
If the user leaves both input fields blank, the query will return
all
dates
between 1-1-1900 and 12-31-2999.
------------------------------

As you can see, both methods I suggested should work.

Copy your code exactly in your reply, and describe in detail where
you
used it.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

message
Dear Al Campagna,

your given code is also not working error occures.

This expression is typed incorrectly, or it is too complex to be
evaluated.
for example, a numeric expression may contain too many complicated
elements,
try simplifying the expression by assigning parts of expression to
variables."
:

Abdul Shakeel,
I don't understand your error
"Too Many Comples Expression"
What is "Comples?"

And, are you saying that the code I suggested works sometimes
and
not
other times?

Try...
= NZ([Forms]![Search Form]![txtStartDate], #1/1/1900#)
And <= NZ([Forms]![Search Form]![txtEndDate], #12/31/2099#)

Just another way to phrase it...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


message
Thanks Campagna for your reply your code works really well but
some
errors
occures whe I try to use it as:
Between Nz([Forms]![frmSelection]![BeginningDate],#01/01/1900#)
And
Nz([Forms]![frmSelection]![EndingDate],#01/01/2099#)
a "Too Many Comples Expression" massage appears could you help me
in
this
metter.

:

Abdul,
This criteria for your Date field. (all on one line)
Between NZ([Enter Start Date],#1/1/1900#) And NZ([Enter
End
Date],#12/31/2999#)
If your date fields are null, all records with a date
between
year
1900
and year 2999 will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


in
message
Thanx for your reply, I have one more question for you, I have
a
bank
Account
Management DB, when user try to look their bank statement a
pop
up
form
appears and ask for "start date", "end date" & "Bank Name" if
user
give
all
the details required, report would be shown otherwise not but
a
problem
occures when it lefts any field blank report not shows any
data
which I
think
wrong, I want if user left empty the "Start Date" and "End
Date"
fields
blank
reprot shows all data of all dates

:

Abdul,
Such is the nature of Autonumbers. Access always
maintains
the
last
autonumber given out, and increments by 1.
If you want a more sequential numbering, try creating a
numeric
field
for your table (ex. RecID), and... on your data entry form,
make
the
default
for that field...
= NZ(DMax("[RecID]","tblYourTable")) + 1
Whenever a new record is created, the RecID will examine
the
table...
get the max number used to date, and add 1.
In your example, after RecID = 10 is deleted from the
table,
the
next
number given out will be RecID = 10.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day
in
your
life."

"Abdul Shakeel" <[email protected]>
wrote
in
message
Hi All,

When I set a field type auto number it generates
incremental
number
every
time when add a new record but when I delete a record it
skips
the
deleted
record number & jumps to next number e.g. I enter record
number 1
2
3
...
10
if I delete number 10 record the next record I enter will
be
11
but
I
want
it
should be 10. Please help me.