Parameter question on Query in Excel

[Leanne]

I need to set up a parameter field for criteria that is user defined. I need
it to be 'Like' (begin with) and I know that I would need to [Enter the
information here] but when I put these together it does not work. Please can
someone help as I am fairly new to this and totally self taught - well with a
bit of help from this site!


I am running the query in Excel using the Wizard. After selecting the data
source and columns required I am editing it via the wizard.

I set criteria on Visit Date field - is not null so that I don’t get any
blanks. I set criteria on Cell address to contains C as there could be B
cells also in this field. And finally I want to set criteria on Site Name -
so that the user can determine which site he wants to see the visit history
of. The sheet/source contains only 4 columns, 3 as mentioned above and a
column which is for date changed. The sheet is to record changes to a
specific column in another sheet so records the data changed, when it was
changed and what cell plus the name of the site for that record.

I am not sure what other information you need so if I have not given
something please just let me know. I am really new to queries and do not
know where to go from here.

A point that just occurred to me is that the sheet is currently empty. Not
sure if that will have an impact or not but thought it best to mention.
Thanks

 

[Leanne M (Aussie)]

Sorry - did not think yestersdays post sucessfully loaded.

 

[FSt1]

just in case,
http://www.dailydoseofexcel.com/archives/2005/03/14/the-like-operator-in-msquery/

regards
FSt1

Sorry - did not think yestersdays post sucessfully loaded.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

I need to set up a parameter field for criteria that is user defined. I need
it to be 'Like' (begin with) and I know that I would need to [Enter the
information here] but when I put these together it does not work. Please can
someone help as I am fairly new to this and totally self taught - well with a
bit of help from this site!


I am running the query in Excel using the Wizard. After selecting the data
source and columns required I am editing it via the wizard.

I set criteria on Visit Date field - is not null so that I don’t get any
blanks. I set criteria on Cell address to contains C as there could be B
cells also in this field. And finally I want to set criteria on Site Name -
so that the user can determine which site he wants to see the visit history
of. The sheet/source contains only 4 columns, 3 as mentioned above and a
column which is for date changed. The sheet is to record changes to a
specific column in another sheet so records the data changed, when it was
changed and what cell plus the name of the site for that record.

I am not sure what other information you need so if I have not given
something please just let me know. I am really new to queries and do not
know where to go from here.

A point that just occurred to me is that the sheet is currently empty. Not
sure if that will have an impact or not but thought it best to mention.
Thanks

 

[Leanne M (Aussie)]

Thanks, I did see your reply to yesterdays post. I got an error message
when I put that post on so thought it did not load hence the reposting today
- should have checked first hey.

I have entered what you suggested and now I do not get an error message -
but I do not get the results I was expecting. I will have a look at this
site but could you just check for me that I have entered the rest correctly?

I have used the Wizzard (what would I do with out them!) and set filter on
Visit Date is not null, Cell Address contains C (and it is displayed as Like
'%C%') - this is because it is actually a cell address ie $c$2 and could
contain cells from column B also but this report is for changes to c only.
Then i went into wizzard to edit and entered the Site Name criteria and
returned data to excel. It works ok but I do not get any results and I know
there are as I have checked the sheet.

Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

just in case,
http://www.dailydoseofexcel.com/archives/2005/03/14/the-like-operator-in-msquery/

regards
FSt1

Sorry - did not think yestersdays post sucessfully loaded.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

I need to set up a parameter field for criteria that is user defined. I need
it to be 'Like' (begin with) and I know that I would need to [Enter the
information here] but when I put these together it does not work. Please can
someone help as I am fairly new to this and totally self taught - well with a
bit of help from this site!


I am running the query in Excel using the Wizard. After selecting the data
source and columns required I am editing it via the wizard.

I set criteria on Visit Date field - is not null so that I don’t get any
blanks. I set criteria on Cell address to contains C as there could be B
cells also in this field. And finally I want to set criteria on Site Name -
so that the user can determine which site he wants to see the visit history
of. The sheet/source contains only 4 columns, 3 as mentioned above and a
column which is for date changed. The sheet is to record changes to a
specific column in another sheet so records the data changed, when it was
changed and what cell plus the name of the site for that record.

I am not sure what other information you need so if I have not given
something please just let me know. I am really new to queries and do not
know where to go from here.

A point that just occurred to me is that the sheet is currently empty. Not
sure if that will have an impact or not but thought it best to mention.
Thanks

 

[FSt1]

hi
can you give examples of your data and perhaps you expected results that you
didn't get?

Regards
FSt1

Thanks, I did see your reply to yesterdays post. I got an error message
when I put that post on so thought it did not load hence the reposting today
- should have checked first hey.

I have entered what you suggested and now I do not get an error message -
but I do not get the results I was expecting. I will have a look at this
site but could you just check for me that I have entered the rest correctly?

I have used the Wizzard (what would I do with out them!) and set filter on
Visit Date is not null, Cell Address contains C (and it is displayed as Like
'%C%') - this is because it is actually a cell address ie $c$2 and could
contain cells from column B also but this report is for changes to c only.
Then i went into wizzard to edit and entered the Site Name criteria and
returned data to excel. It works ok but I do not get any results and I know
there are as I have checked the sheet.

Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

just in case,
http://www.dailydoseofexcel.com/archives/2005/03/14/the-like-operator-in-msquery/

regards
FSt1

Sorry - did not think yestersdays post sucessfully loaded.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


:

I need to set up a parameter field for criteria that is user defined. I need
it to be 'Like' (begin with) and I know that I would need to [Enter the
information here] but when I put these together it does not work. Please can
someone help as I am fairly new to this and totally self taught - well with a
bit of help from this site!


I am running the query in Excel using the Wizard. After selecting the data
source and columns required I am editing it via the wizard.

I set criteria on Visit Date field - is not null so that I don’t get any
blanks. I set criteria on Cell address to contains C as there could be B
cells also in this field. And finally I want to set criteria on Site Name -
so that the user can determine which site he wants to see the visit history
of. The sheet/source contains only 4 columns, 3 as mentioned above and a
column which is for date changed. The sheet is to record changes to a
specific column in another sheet so records the data changed, when it was
changed and what cell plus the name of the site for that record.

I am not sure what other information you need so if I have not given
something please just let me know. I am really new to queries and do not
know where to go from here.

A point that just occurred to me is that the sheet is currently empty. Not
sure if that will have an impact or not but thought it best to mention.
Thanks

 

[Leanne M (Aussie)]

Data in the sheet the report is pulling from is as follows

A B C D
1 01/05/08 22/04/08 $C$2 Marchwood ERF
2 26/04/08 24/04/08 $B$3 Porstmouth ERF
3 01/05/08 24/04/08 $C$3 Portsmouth ERF

Column a = Visit Date (data that was changed in another sheet)
Column B = Date that the change was made
Column c = Cell address of what was changed
Column D = Site name for that row - Row 2 is the record for Marchwood, Row 3
is the record for Portsmouth and so on.

The cell address of what was changed not only tells me the record that was
changed but if it is column c that was changed it is a visit date and if it
was column b that was changed it is an invoice date that was changed.

I ran Visit Date a as is not null, Cell Address as contains C and Site Name
as the user defined entry and entered Portsmouth. Hence I expected to see

01/05/08 24/04/08 $C$3 Portsmouth ERF

- though in truth in the report I just need columns A and D to be visible
to the user.

This report is to show a history of visits for whatever site the user
requests. I will then make the same report for invoice history.

Hope this explains enough but please let me know if you have more questions.
Anything I can do to help you help me!
Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

hi
can you give examples of your data and perhaps you expected results that you
didn't get?

Regards
FSt1

Thanks, I did see your reply to yesterdays post. I got an error message
when I put that post on so thought it did not load hence the reposting today
- should have checked first hey.

I have entered what you suggested and now I do not get an error message -
but I do not get the results I was expecting. I will have a look at this
site but could you just check for me that I have entered the rest correctly?

I have used the Wizzard (what would I do with out them!) and set filter on
Visit Date is not null, Cell Address contains C (and it is displayed as Like
'%C%') - this is because it is actually a cell address ie $c$2 and could
contain cells from column B also but this report is for changes to c only.
Then i went into wizzard to edit and entered the Site Name criteria and
returned data to excel. It works ok but I do not get any results and I know
there are as I have checked the sheet.

Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

just in case,
http://www.dailydoseofexcel.com/archives/2005/03/14/the-like-operator-in-msquery/

regards
FSt1

:

Sorry - did not think yestersdays post sucessfully loaded.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


:

I need to set up a parameter field for criteria that is user defined. I need
it to be 'Like' (begin with) and I know that I would need to [Enter the
information here] but when I put these together it does not work. Please can
someone help as I am fairly new to this and totally self taught - well with a
bit of help from this site!


I am running the query in Excel using the Wizard. After selecting the data
source and columns required I am editing it via the wizard.

I set criteria on Visit Date field - is not null so that I don’t get any
blanks. I set criteria on Cell address to contains C as there could be B
cells also in this field. And finally I want to set criteria on Site Name -
so that the user can determine which site he wants to see the visit history
of. The sheet/source contains only 4 columns, 3 as mentioned above and a
column which is for date changed. The sheet is to record changes to a
specific column in another sheet so records the data changed, when it was
changed and what cell plus the name of the site for that record.

I am not sure what other information you need so if I have not given
something please just let me know. I am really new to queries and do not
know where to go from here.

A point that just occurred to me is that the sheet is currently empty. Not
sure if that will have an impact or not but thought it best to mention.
Thanks

 

[FSt1]

hi leanne,
i just did a test on your data. i copied your data and pasted it seveal time
to create a dummy table. i created an MSQ and for critera (in the wizard) i
use.....Like %C%....
the MSQ returned all C location.

in the MSQ criteria pane , it displayed as .....like '%C%'

i also use the Contains keyword. .....contains C...
and i got all C's again. you might try that.

so i am now unsure as to what your are doing wrong unless it just a syntax
problem.

regards
FSt1

Data in the sheet the report is pulling from is as follows

A B C D
1 01/05/08 22/04/08 $C$2 Marchwood ERF
2 26/04/08 24/04/08 $B$3 Porstmouth ERF
3 01/05/08 24/04/08 $C$3 Portsmouth ERF

Column a = Visit Date (data that was changed in another sheet)
Column B = Date that the change was made
Column c = Cell address of what was changed
Column D = Site name for that row - Row 2 is the record for Marchwood, Row 3
is the record for Portsmouth and so on.

The cell address of what was changed not only tells me the record that was
changed but if it is column c that was changed it is a visit date and if it
was column b that was changed it is an invoice date that was changed.

I ran Visit Date a as is not null, Cell Address as contains C and Site Name
as the user defined entry and entered Portsmouth. Hence I expected to see

01/05/08 24/04/08 $C$3 Portsmouth ERF

- though in truth in the report I just need columns A and D to be visible
to the user.

This report is to show a history of visits for whatever site the user
requests. I will then make the same report for invoice history.

Hope this explains enough but please let me know if you have more questions.
Anything I can do to help you help me!
Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

hi
can you give examples of your data and perhaps you expected results that you
didn't get?

Regards
FSt1

Thanks, I did see your reply to yesterdays post. I got an error message
when I put that post on so thought it did not load hence the reposting today
- should have checked first hey.

I have entered what you suggested and now I do not get an error message -
but I do not get the results I was expecting. I will have a look at this
site but could you just check for me that I have entered the rest correctly?

I have used the Wizzard (what would I do with out them!) and set filter on
Visit Date is not null, Cell Address contains C (and it is displayed as Like
'%C%') - this is because it is actually a cell address ie $c$2 and could
contain cells from column B also but this report is for changes to c only.
Then i went into wizzard to edit and entered the Site Name criteria and
returned data to excel. It works ok but I do not get any results and I know
there are as I have checked the sheet.

Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


:

just in case,
http://www.dailydoseofexcel.com/archives/2005/03/14/the-like-operator-in-msquery/

regards
FSt1

:

Sorry - did not think yestersdays post sucessfully loaded.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


:

I need to set up a parameter field for criteria that is user defined. I need
it to be 'Like' (begin with) and I know that I would need to [Enter the
information here] but when I put these together it does not work. Please can
someone help as I am fairly new to this and totally self taught - well with a
bit of help from this site!


I am running the query in Excel using the Wizard. After selecting the data
source and columns required I am editing it via the wizard.

I set criteria on Visit Date field - is not null so that I don’t get any
blanks. I set criteria on Cell address to contains C as there could be B
cells also in this field. And finally I want to set criteria on Site Name -
so that the user can determine which site he wants to see the visit history
of. The sheet/source contains only 4 columns, 3 as mentioned above and a
column which is for date changed. The sheet is to record changes to a
specific column in another sheet so records the data changed, when it was
changed and what cell plus the name of the site for that record.

I am not sure what other information you need so if I have not given
something please just let me know. I am really new to queries and do not
know where to go from here.

A point that just occurred to me is that the sheet is currently empty. Not
sure if that will have an impact or not but thought it best to mention.
Thanks

 

[Leanne M (Aussie)]

Hi,

Sorry for the delay - I guess the site has been playing up for me as I could
not reply or post until now!

I have tried this again as I noticed that you did not mention anything about
Visit Date = is not null. This is what is causing me not to see the results
I want. I have changed it to Visit date = Like '%' and I get what I want.

Thank you for your help with it.

Do you know of any good sources for notes on creating queries in Excel? I
must be doing something wrong if I can not get these to run on other
computers even after declaring the data source.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

hi leanne,
i just did a test on your data. i copied your data and pasted it seveal time
to create a dummy table. i created an MSQ and for critera (in the wizard) i
use.....Like %C%....
the MSQ returned all C location.

in the MSQ criteria pane , it displayed as .....like '%C%'

i also use the Contains keyword. .....contains C...
and i got all C's again. you might try that.

so i am now unsure as to what your are doing wrong unless it just a syntax
problem.

regards
FSt1

Data in the sheet the report is pulling from is as follows

A B C D
1 01/05/08 22/04/08 $C$2 Marchwood ERF
2 26/04/08 24/04/08 $B$3 Porstmouth ERF
3 01/05/08 24/04/08 $C$3 Portsmouth ERF

Column a = Visit Date (data that was changed in another sheet)
Column B = Date that the change was made
Column c = Cell address of what was changed
Column D = Site name for that row - Row 2 is the record for Marchwood, Row 3
is the record for Portsmouth and so on.

The cell address of what was changed not only tells me the record that was
changed but if it is column c that was changed it is a visit date and if it
was column b that was changed it is an invoice date that was changed.

I ran Visit Date a as is not null, Cell Address as contains C and Site Name
as the user defined entry and entered Portsmouth. Hence I expected to see

01/05/08 24/04/08 $C$3 Portsmouth ERF

- though in truth in the report I just need columns A and D to be visible
to the user.

This report is to show a history of visits for whatever site the user
requests. I will then make the same report for invoice history.

Hope this explains enough but please let me know if you have more questions.
Anything I can do to help you help me!
Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

hi
can you give examples of your data and perhaps you expected results that you
didn't get?

Regards
FSt1

:

Thanks, I did see your reply to yesterdays post. I got an error message
when I put that post on so thought it did not load hence the reposting today
- should have checked first hey.

I have entered what you suggested and now I do not get an error message -
but I do not get the results I was expecting. I will have a look at this
site but could you just check for me that I have entered the rest correctly?

I have used the Wizzard (what would I do with out them!) and set filter on
Visit Date is not null, Cell Address contains C (and it is displayed as Like
'%C%') - this is because it is actually a cell address ie $c$2 and could
contain cells from column B also but this report is for changes to c only.
Then i went into wizzard to edit and entered the Site Name criteria and
returned data to excel. It works ok but I do not get any results and I know
there are as I have checked the sheet.

Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


:

just in case,
http://www.dailydoseofexcel.com/archives/2005/03/14/the-like-operator-in-msquery/

regards
FSt1

:

Sorry - did not think yestersdays post sucessfully loaded.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


:

I need to set up a parameter field for criteria that is user defined. I need
it to be 'Like' (begin with) and I know that I would need to [Enter the
information here] but when I put these together it does not work. Please can
someone help as I am fairly new to this and totally self taught - well with a
bit of help from this site!


I am running the query in Excel using the Wizard. After selecting the data
source and columns required I am editing it via the wizard.

I set criteria on Visit Date field - is not null so that I don’t get any
blanks. I set criteria on Cell address to contains C as there could be B
cells also in this field. And finally I want to set criteria on Site Name -
so that the user can determine which site he wants to see the visit history
of. The sheet/source contains only 4 columns, 3 as mentioned above and a
column which is for date changed. The sheet is to record changes to a
specific column in another sheet so records the data changed, when it was
changed and what cell plus the name of the site for that record.

I am not sure what other information you need so if I have not given
something please just let me know. I am really new to queries and do not
know where to go from here.

A point that just occurred to me is that the sheet is currently empty. Not
sure if that will have an impact or not but thought it best to mention.
Thanks

 

[Leanne M (Aussie)]

AAAAARRRRRRGGGGGGGG

Now I can not get it to work for Invoice History (Cell address with B in)

I have done exactly the same thing but can only get responses for one site
and not others.

I even tried editing the Visit report and changing the c to a b and then
saving it as Invoice - just to make sure I was not doing anything wrong. It
still does not work. I really need my head examining for agreeing to do this
- I should have admited it was beyond my capacity.