Field value in a query

[deejay_totoro]

Hello,

I would like to reference a field value in a query, and not sure how to do this!

For example, I would like to query all purchases by [myTable].[my_field]. When I try this in a query, Access keeps prompting me for a value and doesnt use the actual value in the table.

How might I achieve this?

Many thanks,

dj_T

 

[MStef]

How query knows which PURCHASE you want ??

 

[deejay_totoro]

Only one date

There is actually only one record and one field in the date table.

cheers.

dj_T

 

[Rabbie]

If you are being prompted for a value it is because access can't find the field. Check your spelling carefully.

Try using the Query Building Wizard and that should fix your problem

 

[deejay_totoro]

Build...

Hello,

Thanks for the reply.

I did try using the build feature. still get a prompt...

Cheers!

 

[Rabbie]

Waht value is it prompting for?. Post the SQL from the SQL view in Builder so we can see what is going on.

 

[deejay_totoro]

Sql

Here is a simple version of the SQL:

SELECT tblDate.date
FROM tblDate
WHERE (((tblDate.date)=[tblPromptDate]![promptDate]));

Cheers!

 

[Rabbie]

Here is a simple version of the SQL:

SELECT tblDate.date
FROM tblDate
WHERE (((tblDate.date)=[tblPromptDate]![promptDate]));

Cheers!

Is tblPromptDate a table or a form. If it is a form then the form needs to open when you run the query. If it is a table you need to tell Access which value you want. I would try using tblPromtDate.promptdate

 

[MStef]

if it is only one record, then you don't need a criteria.
Or try it with DLOKUP function.

 

[deejay_totoro]

field...

It is actually field.

I tried your suggestion but didnt work

However I suppose another to do it would be to put the date on a form and then reference the form (which works)...

but I would have preferred to just reference the table.field from within a query without having to use a form...

Cheers!

 

[Rabbie]

It is actually field.

Cheers!

I know its a field but a field on a form or field in a table. Try to help us to help you.

 

[neileg]

You're going about this the wrong way. Even if your table only has one record, Access still wont recognise that and needs some other help to identify the value. This syntax

=[tblPromptDate]![promptDate]

won't work because it doesn't mean anything.

You have two obvious choices.
1) you can use tblPromptDate in your query by joining it to your other table. Your WHERE clause would become WHERE tblDate.date=tblPromptDate.promptDate (This is what Rabbie already advised)
2) you can use a domain function such as DLookup(), DMax() or whatever (which is what MStef advised)

Your use of the word 'prompt' in your object names suggests that this is some form of user input. If this is the case, you could capture this in a form and use that in your WHERE clause. The syntax would be similar to what you have tried to use, Forms!MyForm!MyTextBox

A final point is that 'date' is a reserved word in Access because it is the name of a function and shouldn't be used as an object name. This can cause all kinds of bother, though I don't think that is why your query doesn't work, in this case.

 

[deejay_totoro]

table

Thanks for your replies.