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!)
"FSt1" wrote:
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
"Leanne M (Aussie)" wrote:
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!)
"FSt1" wrote:
hi
can you give examples of your data and perhaps you expected results that you
didn't get?
Regards
FSt1
"Leanne M (Aussie)" wrote:
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!)
"FSt1" wrote:
just in case,
http://www.dailydoseofexcel.com/arch...or-in-msquery/
regards
FSt1
"Leanne M (Aussie)" wrote:
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!)
"Leanne" wrote:
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 dont 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