Query By Form - criteria expression - convert text to numeric

[Bob Watson]

Access 97

I need to do a query by form and understand that
I need to develop a form with "unbound" controls ...
I will be using a comboBox to chose either

30
60
90
180

I suppose that this will really be text (string) data.

Upon clicking OK, I need to cause the query to take
place with a criteria that looks like this:

Field Date
Criteria Date>Date()-Forms![myControlForm]!.[myComboBox.text]

but I would think I really need to convert that [myComboBox.text] to
an integer

What is the proper conversion call?

StrToInt(Forms![myControlForm]!.[myComboBox.text])
????????

One more question ... I only know how to load the comboBox
with a table. Is there a way to just "hand load" this box
using the "Properties"?

TIA,
Bob

 

[Allen Browne]

Set these properties for your combo:
Row Source Type Value List
Row Source 30, 60, 90, 180
Format General Number

The values are numeric (because they are not in quotes) and Access
recognises the value as numeric (because of the Format.)

You can now calculate dates forward as:
DateAdd("d", Nz([myComboBox],0), Date())
or backwards:
DateAdd("d", -Nz([myComboBox],0), Date())

 

[Bob Watson]

In trying to use this criteria, I get a message:

Function isn't available in expressions in querry expression
'(((ProjectInformation.Date)>DateAdd("d",90,Date())))


This is when I just type that criteria in ... not "plucking"
it from a QBF form control.

I am using Access 97, if that matters.

Thanks,
Bob
___________________________________________

Set these properties for your combo:
Row Source Type Value List
Row Source 30, 60, 90, 180
Format General Number

The values are numeric (because they are not in quotes) and Access
recognises the value as numeric (because of the Format.)

You can now calculate dates forward as:
DateAdd("d", Nz([myComboBox],0), Date())
or backwards:
DateAdd("d", -Nz([myComboBox],0), Date())

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access 97

I need to do a query by form and understand that
I need to develop a form with "unbound" controls ...
I will be using a comboBox to chose either

30
60
90
180

I suppose that this will really be text (string) data.

Upon clicking OK, I need to cause the query to take
place with a criteria that looks like this:

Field Date
Criteria Date>Date()-Forms![myControlForm]!.[myComboBox.text]

but I would think I really need to convert that [myComboBox.text] to
an integer

What is the proper conversion call?

StrToInt(Forms![myControlForm]!.[myComboBox.text])
????????

One more question ... I only know how to load the comboBox
with a table. Is there a way to just "hand load" this box
using the "Properties"?

 

[Allen Browne]

The function should be available unless there is a references problem:
http://allenbrowne.com/ser-38.html

You can use the [myComboBox]+90 or [myComboBox]-90 if you prefer.

Date is a reserved Word in VBA, so not a good name for a field. I don't
think this would be the issue in this context, but it certainly could be in
the context of a form.

A97 will be fine with this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In trying to use this criteria, I get a message:

Function isn't available in expressions in querry expression
'(((ProjectInformation.Date)>DateAdd("d",90,Date())))


This is when I just type that criteria in ... not "plucking"
it from a QBF form control.

I am using Access 97, if that matters.

Thanks,
Bob
___________________________________________

Set these properties for your combo:
Row Source Type Value List
Row Source 30, 60, 90, 180
Format General Number

The values are numeric (because they are not in quotes) and Access
recognises the value as numeric (because of the Format.)

You can now calculate dates forward as:
DateAdd("d", Nz([myComboBox],0), Date())
or backwards:
DateAdd("d", -Nz([myComboBox],0), Date())

Access 97

I need to do a query by form and understand that
I need to develop a form with "unbound" controls ...
I will be using a comboBox to chose either

30
60
90
180

I suppose that this will really be text (string) data.

Upon clicking OK, I need to cause the query to take
place with a criteria that looks like this:

Field Date
Criteria Date>Date()-Forms![myControlForm]!.[myComboBox.text]

but I would think I really need to convert that [myComboBox.text] to
an integer

What is the proper conversion call?

StrToInt(Forms![myControlForm]!.[myComboBox.text])
????????

One more question ... I only know how to load the comboBox
with a table. Is there a way to just "hand load" this box
using the "Properties"?

 

[Bob Watson]

Allen, I don't see this "code window" to which
you speak
To see what libraries an Access project has referenced, open any code window
(e.g. press Ctrl+G), and choose References from the Tools menu.


And consequently I cannot find References on the Tools menu.

Thanks,
Bob
______________

The function should be available unless there is a references problem:
http://allenbrowne.com/ser-38.html

You can use the [myComboBox]+90 or [myComboBox]-90 if you prefer.

Date is a reserved Word in VBA, so not a good name for a field. I don't
think this would be the issue in this context, but it certainly could be
in the context of a form.

A97 will be fine with this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In trying to use this criteria, I get a message:

Function isn't available in expressions in querry expression
'(((ProjectInformation.Date)>DateAdd("d",90,Date())))


This is when I just type that criteria in ... not "plucking"
it from a QBF form control.

I am using Access 97, if that matters.

Thanks,
Bob
___________________________________________

Set these properties for your combo:
Row Source Type Value List
Row Source 30, 60, 90, 180
Format General Number

The values are numeric (because they are not in quotes) and Access
recognises the value as numeric (because of the Format.)

You can now calculate dates forward as:
DateAdd("d", Nz([myComboBox],0), Date())
or backwards:
DateAdd("d", -Nz([myComboBox],0), Date())

Access 97

I need to do a query by form and understand that
I need to develop a form with "unbound" controls ...
I will be using a comboBox to chose either

30
60
90
180

I suppose that this will really be text (string) data.

Upon clicking OK, I need to cause the query to take
place with a criteria that looks like this:

Field Date
Criteria Date>Date()-Forms![myControlForm]!.[myComboBox.text]

but I would think I really need to convert that [myComboBox.text] to
an integer

What is the proper conversion call?

StrToInt(Forms![myControlForm]!.[myComboBox.text])
????????

One more question ... I only know how to load the comboBox
with a table. Is there a way to just "hand load" this box
using the "Properties"?

 

[Allen Browne]

Hold down the Ctrl key, and press G. Access should open the immediate
window.

Alt+F11 should also do it.

Or choose the Modules tab of the Database window, and click New.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I don't see this "code window" to which
you speak
To see what libraries an Access project has referenced, open any code
window (e.g. press Ctrl+G), and choose References from the Tools menu.


And consequently I cannot find References on the Tools menu.

Thanks,
Bob
______________

The function should be available unless there is a references problem:
http://allenbrowne.com/ser-38.html

You can use the [myComboBox]+90 or [myComboBox]-90 if you prefer.

Date is a reserved Word in VBA, so not a good name for a field. I don't
think this would be the issue in this context, but it certainly could be
in the context of a form.

A97 will be fine with this.

In trying to use this criteria, I get a message:

Function isn't available in expressions in querry expression
'(((ProjectInformation.Date)>DateAdd("d",90,Date())))


This is when I just type that criteria in ... not "plucking"
it from a QBF form control.

I am using Access 97, if that matters.

Thanks,
Bob
___________________________________________


Set these properties for your combo:
Row Source Type Value List
Row Source 30, 60, 90, 180
Format General Number

The values are numeric (because they are not in quotes) and Access
recognises the value as numeric (because of the Format.)

You can now calculate dates forward as:
DateAdd("d", Nz([myComboBox],0), Date())
or backwards:
DateAdd("d", -Nz([myComboBox],0), Date())

Access 97

I need to do a query by form and understand that
I need to develop a form with "unbound" controls ...
I will be using a comboBox to chose either

30
60
90
180

I suppose that this will really be text (string) data.

Upon clicking OK, I need to cause the query to take
place with a criteria that looks like this:

Field Date
Criteria Date>Date()-Forms![myControlForm]!.[myComboBox.text]

but I would think I really need to convert that [myComboBox.text] to
an integer

What is the proper conversion call?

StrToInt(Forms![myControlForm]!.[myComboBox.text])
????????

One more question ... I only know how to load the comboBox
with a table. Is there a way to just "hand load" this box
using the "Properties"?

 

[Bob Watson]

OK -

MISSING: Microsoft Calendar Control 8.0

which sounds like it likely
has something to do with Date() not working. So, I did the BROWSE and found
the file you made reference to

c:\program files\microsoft office\office\msacc8.olb

After I clicked OK it still showed MISSING. So, I did the
RegSvr32 "c:\program files\microsoft office\office\msacc8.olb"

and get this message:
__

C:\c:\program files\microsoft office\office\msacc8.olb was loaded but the
DllRegisterServer entry point was not found.

c:\program files\microsoft office\office\msacc8.olb does not appear to be
a .DLL
or a .OCX file
__

Any ideas??
Thanks,
Bob

____________________________________

Hold down the Ctrl key, and press G. Access should open the immediate
window.

Alt+F11 should also do it.

Or choose the Modules tab of the Database window, and click New.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I don't see this "code window" to which
you speak
To see what libraries an Access project has referenced, open any code
window (e.g. press Ctrl+G), and choose References from the Tools menu.


And consequently I cannot find References on the Tools menu.

Thanks,
Bob
______________

The function should be available unless there is a references problem:
http://allenbrowne.com/ser-38.html

You can use the [myComboBox]+90 or [myComboBox]-90 if you prefer.

Date is a reserved Word in VBA, so not a good name for a field. I don't
think this would be the issue in this context, but it certainly could be
in the context of a form.

A97 will be fine with this.


In trying to use this criteria, I get a message:

Function isn't available in expressions in querry expression
'(((ProjectInformation.Date)>DateAdd("d",90,Date())))


This is when I just type that criteria in ... not "plucking"
it from a QBF form control.

I am using Access 97, if that matters.

Thanks,
Bob
___________________________________________


Set these properties for your combo:
Row Source Type Value List
Row Source 30, 60, 90, 180
Format General Number

The values are numeric (because they are not in quotes) and Access
recognises the value as numeric (because of the Format.)

You can now calculate dates forward as:
DateAdd("d", Nz([myComboBox],0), Date())
or backwards:
DateAdd("d", -Nz([myComboBox],0), Date())

Access 97

I need to do a query by form and understand that
I need to develop a form with "unbound" controls ...
I will be using a comboBox to chose either

30
60
90
180

I suppose that this will really be text (string) data.

Upon clicking OK, I need to cause the query to take
place with a criteria that looks like this:

Field Date
Criteria Date>Date()-Forms![myControlForm]!.[myComboBox.text]

but I would think I really need to convert that [myComboBox.text] to
an integer

What is the proper conversion call?

StrToInt(Forms![myControlForm]!.[myComboBox.text])
????????

One more question ... I only know how to load the comboBox
with a table. Is there a way to just "hand load" this box
using the "Properties"?

 

[Allen Browne]

Remove the reference from your database.
Save.
Close.

Unregister the library with:
RegSvr32 /u "c:\program files\microsoft office\office\msacc8.olb"

Register the libraray again:
RegSvr32 "c:\program files\microsoft office\office\msacc8.olb"

Open your database, and add the reference again.

This kind of issue is common, and you will strike it again when you convert
to a different version of Access. Would you consider dropping the calcendar
control, and using a simple Access form like this one:
http://allenbrowne.com/ser-51.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK -

MISSING: Microsoft Calendar Control 8.0

which sounds like it likely
has something to do with Date() not working. So, I did the BROWSE and
found
the file you made reference to

c:\program files\microsoft office\office\msacc8.olb

After I clicked OK it still showed MISSING. So, I did the
RegSvr32 "c:\program files\microsoft office\office\msacc8.olb"

and get this message:
__

C:\c:\program files\microsoft office\office\msacc8.olb was loaded but the
DllRegisterServer entry point was not found.

c:\program files\microsoft office\office\msacc8.olb does not appear to be
a .DLL
or a .OCX file
__

Any ideas??
Thanks,
Bob

____________________________________

Hold down the Ctrl key, and press G. Access should open the immediate
window.

Alt+F11 should also do it.

Or choose the Modules tab of the Database window, and click New.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I don't see this "code window" to which
you speak
To see what libraries an Access project has referenced, open any code
window (e.g. press Ctrl+G), and choose References from the Tools menu.


And consequently I cannot find References on the Tools menu.

Thanks,
Bob
______________

The function should be available unless there is a references problem:
http://allenbrowne.com/ser-38.html

You can use the [myComboBox]+90 or [myComboBox]-90 if you prefer.

Date is a reserved Word in VBA, so not a good name for a field. I don't
think this would be the issue in this context, but it certainly could
be in the context of a form.

A97 will be fine with this.


In trying to use this criteria, I get a message:

Function isn't available in expressions in querry expression
'(((ProjectInformation.Date)>DateAdd("d",90,Date())))


This is when I just type that criteria in ... not "plucking"
it from a QBF form control.

I am using Access 97, if that matters.

Thanks,
Bob
___________________________________________


Set these properties for your combo:
Row Source Type Value List
Row Source 30, 60, 90, 180
Format General Number

The values are numeric (because they are not in quotes) and Access
recognises the value as numeric (because of the Format.)

You can now calculate dates forward as:
DateAdd("d", Nz([myComboBox],0), Date())
or backwards:
DateAdd("d", -Nz([myComboBox],0), Date())

Access 97

I need to do a query by form and understand that
I need to develop a form with "unbound" controls ...
I will be using a comboBox to chose either

30
60
90
180

I suppose that this will really be text (string) data.

Upon clicking OK, I need to cause the query to take
place with a criteria that looks like this:

Field Date
Criteria Date>Date()-Forms![myControlForm]!.[myComboBox.text]

but I would think I really need to convert that [myComboBox.text] to
an integer

What is the proper conversion call?

StrToInt(Forms![myControlForm]!.[myComboBox.text])
????????

One more question ... I only know how to load the comboBox
with a table. Is there a way to just "hand load" this box
using the "Properties"?

 

[Bob Watson]

Allen,

I certainly like that Access form you reference, but let
me make sure we agree on my problem.

I have a criteria in a query :

DateAdd("d", Nz([myComboBox],0), Date())
DateAdd("d", -Nz([myComboBox],0), Date())

or the one I prefer is Date()-30

If I remove this function call, all is well ... the
system does not do what I want, but I get no error
message. By whatever means I pass the date to this criteria
I think I am going to have a problem. BUT, yes I like
your "date picking calendar" ... If I could include
that date picker into a QBF form and let the user
pick the date and then take the date over to the
criteria, I think that would be good.

By the way, I appreciate all your help. And I have
looked at your tutorial-like web pages and they are
very helpful. My background is really using Delphi
against X-Base files. Several years back I tried using
Delphi against Access files and performance was
extremely slow - I guess MS has come up with a new
"Jet DB engine" and now it seems to perform quite well.
So, I might switch to Delphi and Access DB as my "normal"
mode of operation. This particular client insisted on
continuing to use the Access Language built against
the Access DB and even furthermore insisted on NOT
upgrading but sticking with Access 97. The term
"firing a client" has crossed my mind, but I have
learned a lot during this frustrating process.

I have downloaded your calendar97.zip or whatever
the name was and I will take a look at using it.

So, back to my problem ... I guess I need to:

Remove the reference from your database. - which is the call to DateAdd
Save.
Close.

Unregister the library with:
RegSvr32 /u "c:\program files\microsoft office\office\msacc8.olb"

Register the libraray again:
RegSvr32 "c:\program files\microsoft office\office\msacc8.olb"

Open your database, and add the reference again.

Which I will do pronto - Thank you sir,
Bob

PS: Do you mind if I email you OFF LIST??

__________
__________
______________________________________________

Remove the reference from your database.
Save.
Close.

Unregister the library with:
RegSvr32 /u "c:\program files\microsoft office\office\msacc8.olb"

Register the libraray again:
RegSvr32 "c:\program files\microsoft office\office\msacc8.olb"

Open your database, and add the reference again.

This kind of issue is common, and you will strike it again when you
convert to a different version of Access. Would you consider dropping the
calcendar control, and using a simple Access form like this one:
http://allenbrowne.com/ser-51.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK -

MISSING: Microsoft Calendar Control 8.0

which sounds like it likely
has something to do with Date() not working. So, I did the BROWSE and
found
the file you made reference to

c:\program files\microsoft office\office\msacc8.olb

After I clicked OK it still showed MISSING. So, I did the
RegSvr32 "c:\program files\microsoft office\office\msacc8.olb"

and get this message:
__

C:\c:\program files\microsoft office\office\msacc8.olb was loaded but
the
DllRegisterServer entry point was not found.

c:\program files\microsoft office\office\msacc8.olb does not appear to
be a .DLL
or a .OCX file
__

Any ideas??
Thanks,
Bob

____________________________________

Hold down the Ctrl key, and press G. Access should open the immediate
window.

Alt+F11 should also do it.

Or choose the Modules tab of the Database window, and click New.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I don't see this "code window" to which
you speak
To see what libraries an Access project has referenced, open any code
window (e.g. press Ctrl+G), and choose References from the Tools menu.


And consequently I cannot find References on the Tools menu.

Thanks,
Bob
______________

The function should be available unless there is a references problem:
http://allenbrowne.com/ser-38.html

You can use the [myComboBox]+90 or [myComboBox]-90 if you prefer.

Date is a reserved Word in VBA, so not a good name for a field. I
don't think this would be the issue in this context, but it certainly
could be in the context of a form.

A97 will be fine with this.


In trying to use this criteria, I get a message:

Function isn't available in expressions in querry expression
'(((ProjectInformation.Date)>DateAdd("d",90,Date())))


This is when I just type that criteria in ... not "plucking"
it from a QBF form control.

I am using Access 97, if that matters.

Thanks,
Bob
___________________________________________


Set these properties for your combo:
Row Source Type Value List
Row Source 30, 60, 90, 180
Format General Number

The values are numeric (because they are not in quotes) and Access
recognises the value as numeric (because of the Format.)

You can now calculate dates forward as:
DateAdd("d", Nz([myComboBox],0), Date())
or backwards:
DateAdd("d", -Nz([myComboBox],0), Date())

Access 97

I need to do a query by form and understand that
I need to develop a form with "unbound" controls ...
I will be using a comboBox to chose either

30
60
90
180

I suppose that this will really be text (string) data.

Upon clicking OK, I need to cause the query to take
place with a criteria that looks like this:

Field Date
Criteria Date>Date()-Forms![myControlForm]!.[myComboBox.text]

but I would think I really need to convert that [myComboBox.text]
to
an integer

What is the proper conversion call?

StrToInt(Forms![myControlForm]!.[myComboBox.text])
????????

One more question ... I only know how to load the comboBox
with a table. Is there a way to just "hand load" this box
using the "Properties"?

 

[Allen Browne]

PS: Do you mind if I email you OFF LIST??

Best if we keep it here, Bob. Others can benefit, and contribute solutions
for you also.