Creating query in VBA

J

jgnasser

Registered User.

Local time

Today, 18:34

Joined

Aug 25, 2003

Messages

54

• Jan 15, 2004

• #1

I have a query which passes on values to a report. The criteria used for the query changes depending on what options the user selects in a user form. I'd like to create the query in VBA so that I can use IF, THEN, ELSE to determine what set of criteria to use. If someone has a code to do this please share.

 

Mile-O

Back once again...

Local time

Today, 15:34

Joined

Dec 10, 2002

Messages

11,310

• Jan 15, 2004

• #2

Different criteria in the same field or criteria on different fields?

 

J

jgnasser

Registered User.

Local time

Today, 18:34

Joined

Aug 25, 2003

Messages

54

• Jan 15, 2004

• #3

Currently just one criteria is changing. Specifically, its picking out data for various dates so my criteria is begin date and end date with an option of just picking a month so the code computes the dates as the first and last dates of the month.

 

Mile-O

Back once again...

Local time

Today, 15:34

Joined

Dec 10, 2002

Messages

11,310

• Jan 15, 2004

• #4

Are you not going to specify which year?

You'd be better just putting a before and end on the form, let the user specify the dates they want, and then reference the form in a query.

There's no need to use VBA for this.

 

J

jgnasser

Registered User.

Local time

Today, 18:34

Joined

Aug 25, 2003

Messages

54

• Jan 15, 2004

• #5

OK I could do that, actually I have thatalready, only thought of making it easy to produce the reports by picking a month and year eg "January 2004" or "November 2003".

Thanks.

 

Mile-O

Back once again...

Local time

Today, 15:34

Joined

Dec 10, 2002

Messages

11,310

• Jan 15, 2004

• #6

Quick way, make a table with three fields.

Display
StartDate
EndDate

In the table put, respectively,

December, 2003
01/12/03
31/12/03

Have a combobox on your form. Three columns, and select the thre fields (sort by EndDate descending)

Hide the 2 date columns

Put two textboxes on your form. txtStartDate and txtEndDate. Hide them. Set the ControlSources to =[CboMyCombo].[Column].(1) and =[CboMyCombo].[Column].(2) respectively.

Reference these two textboxes in your query with the Between And statements.

 

J

jgnasser

Registered User.

Local time

Today, 18:34

Joined

Aug 25, 2003

Messages

54

• Jan 16, 2004

• #7

Thanks, this worked fine and I further linked it to work with my date picker.