A couple of questions about queries

[scubadiver]

1) Is it possible to allow users to create their own ad-hoc queries through
a form?

2) Is it possible to list (and update) all current queries in a combo box?

I would like to do both

 

[Stefan Hoffmann]

hi,

1) Is it possible to allow users to create their own ad-hoc queries through
a form?

Yes, but it needs coding:

CurrentDb.CreateQueryDef "NameOfQuery", "SQLStatement"

2) Is it possible to list (and update) all current queries in a combo box?

You can use the CurrentDb.QueryDefs collection to enumerate all queries.


mfG
--> stefan <--

 

[Arvin Meyer [MVP]]

1) Is it possible to allow users to create their own ad-hoc queries
through
a form?

The Access Web Search Wizard is one way of doing it:

http://www.mvps.org/access/modules/mdl0056.htm

You can also look for "BuildCriteria" in Access VBA help for methods of
using that method to build ad-hoc queries.

2) Is it possible to list (and update) all current queries in a combo box?

SELECT Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Type)=5))
ORDER BY Name;

The tilde (~) is the starting character that Access uses to internally save
temporary queries when it compiles Select statements. The Select statement
above tells the query to ignore any temporary queries.

 

[scubadiver]

1) I was hoping I might be able to do it through the design grid.

2) How do I use QueryDefs?

 

[Stefan Hoffmann]

hi,

1) I was hoping I might be able to do it through the design grid.

DoCmd.OpenQuery "Name", acViewDesign

2) How do I use QueryDefs?

Private Sub cmdFillCmb_Click()

Dim qd As DAO.QueryDef

cmbQueries.RowSource = ""
cmbQueries.RowSourceType = "Value List"
For Each qd In CurrentDb.QueryDefs
cmbQueries.AddItem qd.Name
Next qd

End Sub


mfG
--> stefan <--

 

[Arvin Meyer [MVP]]

1) I was hoping I might be able to do it through the design grid.

Open to the Design Grid, then choose View >>> SQL from the menu. Paste in
the following:

SELECT Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Type)=5))
ORDER BY Name;

You may then view it in the Design Grid.

 

[scubadiver]

Hello Arvin,

thanks for that. I didn't think it would be so simple. I have included a
field for "datecreate" and I want to include those queries beyond a certain
date. I have put in the criteria:

2/7/2007

but I still get every date prior.

1) Is it possible to allow users to create their own ad-hoc queries
through
a form?

The Access Web Search Wizard is one way of doing it:

http://www.mvps.org/access/modules/mdl0056.htm

You can also look for "BuildCriteria" in Access VBA help for methods of
using that method to build ad-hoc queries.

2) Is it possible to list (and update) all current queries in a combo box?

SELECT Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Type)=5))
ORDER BY Name;

The tilde (~) is the starting character that Access uses to internally save
temporary queries when it compiles Select statements. The Select statement
above tells the query to ignore any temporary queries.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Douglas J. Steele]

Dates need to be delimited with # characters.

Try

#2/7/2007#

Hopefully you realize that regardless of what your regional settings may be,
thats 7 Feb, 2007.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Arvin,

thanks for that. I didn't think it would be so simple. I have included a
field for "datecreate" and I want to include those queries beyond a
certain
date. I have put in the criteria:

2/7/2007

but I still get every date prior.

1) Is it possible to allow users to create their own ad-hoc queries
through
a form?

The Access Web Search Wizard is one way of doing it:

http://www.mvps.org/access/modules/mdl0056.htm

You can also look for "BuildCriteria" in Access VBA help for methods of
using that method to build ad-hoc queries.

2) Is it possible to list (and update) all current queries in a combo
box?

SELECT Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Type)=5))
ORDER BY Name;

The tilde (~) is the starting character that Access uses to internally
save
temporary queries when it compiles Select statements. The Select
statement
above tells the query to ignore any temporary queries.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[scubadiver]

I formatted [datecreate] to "dd/mm/yyyy" to take out the time.

I took out the format and it is now filtering the date but how do I take out
the time? It isn't necessarily important but it would be useful.

Dates need to be delimited with # characters.

Try

#2/7/2007#

Hopefully you realize that regardless of what your regional settings may be,
thats 7 Feb, 2007.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Arvin,

thanks for that. I didn't think it would be so simple. I have included a
field for "datecreate" and I want to include those queries beyond a
certain
date. I have put in the criteria:

2/7/2007

but I still get every date prior.

1) Is it possible to allow users to create their own ad-hoc queries
through
a form?

The Access Web Search Wizard is one way of doing it:

http://www.mvps.org/access/modules/mdl0056.htm

You can also look for "BuildCriteria" in Access VBA help for methods of
using that method to build ad-hoc queries.

2) Is it possible to list (and update) all current queries in a combo
box?

SELECT Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Type)=5))
ORDER BY Name;

The tilde (~) is the starting character that Access uses to internally
save
temporary queries when it compiles Select statements. The Select
statement
above tells the query to ignore any temporary queries.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Douglas J. Steele]

To remove the time, use the DateValue function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I formatted [datecreate] to "dd/mm/yyyy" to take out the time.

I took out the format and it is now filtering the date but how do I take
out
the time? It isn't necessarily important but it would be useful.

Dates need to be delimited with # characters.

Try

#2/7/2007#

Hopefully you realize that regardless of what your regional settings may
be,
thats 7 Feb, 2007.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Arvin,

thanks for that. I didn't think it would be so simple. I have included
a
field for "datecreate" and I want to include those queries beyond a
certain
date. I have put in the criteria:

2/7/2007

but I still get every date prior.


:


1) Is it possible to allow users to create their own ad-hoc queries
through
a form?

The Access Web Search Wizard is one way of doing it:

http://www.mvps.org/access/modules/mdl0056.htm

You can also look for "BuildCriteria" in Access VBA help for methods
of
using that method to build ad-hoc queries.

2) Is it possible to list (and update) all current queries in a
combo
box?

SELECT Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Type)=5))
ORDER BY Name;

The tilde (~) is the starting character that Access uses to internally
save
temporary queries when it compiles Select statements. The Select
statement
above tells the query to ignore any temporary queries.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[scubadiver]

thanks.

To remove the time, use the DateValue function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I formatted [datecreate] to "dd/mm/yyyy" to take out the time.

I took out the format and it is now filtering the date but how do I take
out
the time? It isn't necessarily important but it would be useful.

Dates need to be delimited with # characters.

Try

#2/7/2007#

Hopefully you realize that regardless of what your regional settings may
be,
thats 7 Feb, 2007.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)



Hello Arvin,

thanks for that. I didn't think it would be so simple. I have included
a
field for "datecreate" and I want to include those queries beyond a
certain
date. I have put in the criteria:

2/7/2007

but I still get every date prior.


:


1) Is it possible to allow users to create their own ad-hoc queries
through
a form?

The Access Web Search Wizard is one way of doing it:

http://www.mvps.org/access/modules/mdl0056.htm

You can also look for "BuildCriteria" in Access VBA help for methods
of
using that method to build ad-hoc queries.

2) Is it possible to list (and update) all current queries in a
combo
box?

SELECT Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Type)=5))
ORDER BY Name;

The tilde (~) is the starting character that Access uses to internally
save
temporary queries when it compiles Select statements. The Select
statement
above tells the query to ignore any temporary queries.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

All SQL dates must be formatted as: mm/dd/yyyy or mm/dd/yy or m/d/yy

There is no alternative unless you use a function to reformat to the correct
SQL date format.

Even dates which include time will be properly included if you add a day to
the end date. For instance, entering 07/03/07 (today's date) will get all
dates previous to today, but only today's dates with a time of 00:00:00.
Dates are stored as a modified Double, so asking for the day without the
time asks for the Integer portion of the Double. If time is involved, you
must add a day or convert all days with the DateValue() function.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I formatted [datecreate] to "dd/mm/yyyy" to take out the time.

I took out the format and it is now filtering the date but how do I take
out
the time? It isn't necessarily important but it would be useful.

Dates need to be delimited with # characters.

Try

#2/7/2007#

Hopefully you realize that regardless of what your regional settings may
be,
thats 7 Feb, 2007.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Arvin,

thanks for that. I didn't think it would be so simple. I have included
a
field for "datecreate" and I want to include those queries beyond a
certain
date. I have put in the criteria:

2/7/2007

but I still get every date prior.


:


1) Is it possible to allow users to create their own ad-hoc queries
through
a form?

The Access Web Search Wizard is one way of doing it:

http://www.mvps.org/access/modules/mdl0056.htm

You can also look for "BuildCriteria" in Access VBA help for methods
of
using that method to build ad-hoc queries.

2) Is it possible to list (and update) all current queries in a
combo
box?

SELECT Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Type)=5))
ORDER BY Name;

The tilde (~) is the starting character that Access uses to internally
save
temporary queries when it compiles Select statements. The Select
statement
above tells the query to ignore any temporary queries.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com