open a subform or hide it based on query results

[vandy]

Hi all,

I have a form which has a combo box containing tool nos. I have a button
which invokes a tool status qry.

I want to open my subform containing all the date transactions only if tool
status is available. if tool is not available it should not open the
transaction subform. Can this be done.


qry:

SELECT tbltoolmaster.ToolID, tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"On-hand","Tool Out") AS Status,
IIf([datereturned] Is Not Null,"",[DateTaken]) AS [Tool out on]
FROM (tbltoolmaster INNER JOIN tbltooltransaction ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID) AND
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken)
GROUP BY tbltoolmaster.ToolID, tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"On-hand","Tool Out"), IIf([datereturned] Is
Not Null,"",[DateTaken])
HAVING (((tbltoolmaster.ToolID)=[forms]![transactionfrm1].[cmbtoolno]));

 

[Marshall Barton]

I have a form which has a combo box containing tool nos. I have a button
which invokes a tool status qry.

I want to open my subform containing all the date transactions only if tool
status is available. if tool is not available it should not open the
transaction subform. Can this be done.

qry:
SELECT tbltoolmaster.ToolID, tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"On-hand","Tool Out") AS Status,
IIf([datereturned] Is Not Null,"",[DateTaken]) AS [Tool out on]
FROM (tbltoolmaster INNER JOIN tbltooltransaction ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID) AND
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken)
GROUP BY tbltoolmaster.ToolID, tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"On-hand","Tool Out"), IIf([datereturned] Is
Not Null,"",[DateTaken])
HAVING (((tbltoolmaster.ToolID)=[forms]![transactionfrm1].[cmbtoolno]));

First, some terminology clarification is needed. You do not
"open" a subform. Subforms can be hidden, but they exist on
a main form and are "open" whenever the main form is open.
If that's not what you mean by "subform", then it's not
clear what you are doing, maybe opening a separate form?

Looking at the query, I wonder if it even runs and, if it
does run, if it produces useful results. Some of the things
in that query that jump out at me are that the Having clause
should be WHERE clause. Since you have not used an
aggregate function (Count, Sum, etc), I don't see any reason
for the query to have a GROUP BY clause.

It's also not clear when you say "only if tool status is
available". My guess is that a tool is "available" if the
datereturned field is not Null. If that's a good guess,
then I don't understand why you would need such an elaborate
query. Why not just search the transaction table for the
tool and a non-null datereturned?

 

[Steve McLeod]

I have a form where I show or hide part of the form depending on an option
control's value. The test isn't important. You test for the presence or
absence of records in your subform then ajust the size of the main form to
hide or unhide the subform. You will have to play with the numbers till you
get the right combination.

Public Const dblTwipsPerInch = 1440 ' one inch

' Values discovered by guess and by golly
Private Const mcdbl_Frm_Right As Double = 2.25
Private Const mcdbl_Frm_Down As Double = 2.25
Private Const mcdbl_Frm_Width As Double = 5.18
Private Const mcdbl_Frm_Height As Double = 4.56

If Me.optGroupChange.Value = Me.optMultiLines.OptionValue Then

DoCmd.MoveSize _
(mcdbl_Frm_Right * dblTwipsPerInch), _
(mcdbl_Frm_Down * dblTwipsPerInch), _
(mcdbl_Frm_Width * dblTwipsPerInch * 2#), _
(mcdbl_Frm_Height * dblTwipsPerInch)

Else

DoCmd.MoveSize _
(mcdbl_Frm_Right * dblTwipsPerInch), _
(mcdbl_Frm_Down * dblTwipsPerInch), _
(mcdbl_Frm_Width * dblTwipsPerInch), _
(mcdbl_Frm_Height * dblTwipsPerInch)

End If

 

[vandy]

Hello Marshall,

I do realize that i am attempting to complicate my task by not approaching
it in the right direction.


I have a table:


Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

how do i accomplish this. I would really appreciate your inputs .

I have a form which has a combo box containing tool nos. I have a button
which invokes a tool status qry.

I want to open my subform containing all the date transactions only if tool
status is available. if tool is not available it should not open the
transaction subform. Can this be done.

qry:
SELECT tbltoolmaster.ToolID, tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"On-hand","Tool Out") AS Status,
IIf([datereturned] Is Not Null,"",[DateTaken]) AS [Tool out on]
FROM (tbltoolmaster INNER JOIN tbltooltransaction ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID) AND
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken)
GROUP BY tbltoolmaster.ToolID, tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"On-hand","Tool Out"), IIf([datereturned] Is
Not Null,"",[DateTaken])
HAVING (((tbltoolmaster.ToolID)=[forms]![transactionfrm1].[cmbtoolno]));

First, some terminology clarification is needed. You do not
"open" a subform. Subforms can be hidden, but they exist on
a main form and are "open" whenever the main form is open.
If that's not what you mean by "subform", then it's not
clear what you are doing, maybe opening a separate form?

Looking at the query, I wonder if it even runs and, if it
does run, if it produces useful results. Some of the things
in that query that jump out at me are that the Having clause
should be WHERE clause. Since you have not used an
aggregate function (Count, Sum, etc), I don't see any reason
for the query to have a GROUP BY clause.

It's also not clear when you say "only if tool status is
available". My guess is that a tool is "available" if the
datereturned field is not Null. If that's a good guess,
then I don't understand why you would need such an elaborate
query. Why not just search the transaction table for the
tool and a non-null datereturned?

 

[Marshall Barton]

I do realize that i am attempting to complicate my task by not approaching
it in the right direction.

I have a table:

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

You should not store a status because it can be calculated.

I think you can get that kind of list of every tool using:

SELECT Toolno,
ToolDesc,
IIf(NameID Is Null, "Available: " & Location,
"Out: " & DateTaken & " - " &
BorrowerName & "(" & Department &")") As Status,
NameID
FROM tbltoolmaster
LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

On your form/report, make the NameID text box invisible and
use three text box in the form header or footer section:
txtAvailable: =Sum(IIf(NameID Is Null, 1, 0)
txtOut: =Sum(IIf(NameID Is Null, 0, 1)
txtTotalTools: =txtAvailable + txtOut

 

[Stuart McCall]

PMFJI. I'm pretty sure this won't work because the Is operator (in VBA) will
expect to be comparing objects:

=Sum(IIf(NameID Is Null, 1, 0)

IMO it should read:

=Sum(Iif(IsNull(NameID), 1, 0)

 

[vandy]

Hello Marshall,

Thanks for replying to my mail.

I tried your query:

SELECT tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc, IIf(NameID Is
Null,"Available: " & Location,"Out: " & DateTaken & " - " & Name & "(" &
Department & ")") AS Status
FROM tbltoolmaster INNER JOIN (tbltooltransaction LEFT JOIN tblname ON
tbltooltransaction.NameID = tblname.ID) ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID;

Result:
ToolNo ToolDesc Status
ET-2 Heat Gun Out: 5/11/2007 - Andrew Warren(Office)
ET-6 Ferrule Tool Out: 6/11/2007 - Angela Ness(Shop)

yes but when i return the tool back it should say that tool ET-6 returned on
this date status available or in. how to implement that.

Also i did not understand the concept of the 3 text boxes. I have a combo
box which calls up all the employees and the id is the bound column of the
combo and one can select the burrower from the combo. how to make use of the
text boxes. I check for the availablility of the tool and if available i
select the name of the burrower and link the transaction details. Is this
correct.

thanks for your patience. I am sorry if this is very basic but i am not very
strong in queries.

I do realize that i am attempting to complicate my task by not approaching
it in the right direction.

I have a table:

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

You should not store a status because it can be calculated.

I think you can get that kind of list of every tool using:

SELECT Toolno,
ToolDesc,
IIf(NameID Is Null, "Available: " & Location,
"Out: " & DateTaken & " - " &
BorrowerName & "(" & Department &")") As Status,
NameID
FROM tbltoolmaster
LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

On your form/report, make the NameID text box invisible and
use three text box in the form header or footer section:
txtAvailable: =Sum(IIf(NameID Is Null, 1, 0)
txtOut: =Sum(IIf(NameID Is Null, 0, 1)
txtTotalTools: =txtAvailable + txtOut

 

[Marshall Barton]

PMFJI. I'm pretty sure this won't work because the Is operator (in VBA) will
expect to be comparing objects:


IMO it should read:

=Sum(Iif(IsNull(NameID), 1, 0)

Control expressions are not processed by the VBA library.
Like queries, they are evaluated by the expression service.

Either expression will work, but Is Null is evaluated
directly in the expression service, while IsNull() requires
a context switch into the VBA library.

 

[Stuart McCall]

Control expressions are not processed by the VBA library.
Like queries, they are evaluated by the expression service.

Either expression will work, but Is Null is evaluated
directly in the expression service, while IsNull() requires
a context switch into the VBA library.

Odd. I understand what you're saying, but I can't get Is Null to work in
VBA. I created a quick test rig:

I made a form with a textbox called txtTest. Then I created this routine in
a module:

Sub test()
Dim ctl As Access.Control
DoCmd.OpenForm "Form1"
Set ctl = Forms!form1.Controls!txtTest
Debug.Print IIf(ctl Is Null, 1, 0)
Set ctl = Nothing
DoCmd.Close acForm, "Form1"
End Sub

When I run it, I get error 424 Object required on the Debug.Print line.

Have I done something wrong? I've never seen this construct work in VBA and
I'm trying to understand what's going on.

 

[Marshall Barton]

Odd. I understand what you're saying, but I can't get Is Null to work in
VBA. I created a quick test rig:

I made a form with a textbox called txtTest. Then I created this routine in
a module:

Sub test()
Dim ctl As Access.Control
DoCmd.OpenForm "Form1"
Set ctl = Forms!form1.Controls!txtTest
Debug.Print IIf(ctl Is Null, 1, 0)
Set ctl = Nothing
DoCmd.Close acForm, "Form1"
End Sub

When I run it, I get error 424 Object required on the Debug.Print line.

Nobody ever said it would work in VBA.

I think you are getting mixed up between control source
expressions and VBA code, which use different libraries.

 

[Marshall Barton]

Hey, it's not all bad. At least the out part of the query
is ok ;-)

You lost part of the second On clause. It needs to be:
ON (TrantoolID = ToolID And DateReturned Is Null)

Note that this is a nonequi join and can not be represented
in the QBE grid. Those parenthesis are critical so make
sure they remain as is. Once you get the query
pasted/edited in SQL view, save it and remember to **never**
switch it to design view or the stupid QBE window will mess
it up again.

The three text boxes in the form header or footer section
were intended to display how many tools are available, how
many are out and the total number of tools.
--
Marsh
MVP [MS Access]

I tried your query:

SELECT tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc, IIf(NameID Is
Null,"Available: " & Location,"Out: " & DateTaken & " - " & Name & "(" &
Department & ")") AS Status
FROM tbltoolmaster INNER JOIN (tbltooltransaction LEFT JOIN tblname ON
tbltooltransaction.NameID = tblname.ID) ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID;

Result:
ToolNo ToolDesc Status
ET-2 Heat Gun Out: 5/11/2007 - Andrew Warren(Office)
ET-6 Ferrule Tool Out: 6/11/2007 - Angela Ness(Shop)

yes but when i return the tool back it should say that tool ET-6 returned on
this date status available or in. how to implement that.

Also i did not understand the concept of the 3 text boxes. I have a combo
box which calls up all the employees and the id is the bound column of the
combo and one can select the burrower from the combo. how to make use of the
text boxes. I check for the availablility of the tool and if available i
select the name of the burrower and link the transaction details. Is this
correct.

You should not store a status because it can be calculated.

I think you can get that kind of list of every tool using:

SELECT Toolno,
ToolDesc,
IIf(NameID Is Null, "Available: " & Location,
"Out: " & DateTaken & " - " &
BorrowerName & "(" & Department &")") As Status,
NameID
FROM tbltoolmaster
LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

On your form/report, make the NameID text box invisible and
use three text box in the form header or footer section:
txtAvailable: =Sum(IIf(NameID Is Null, 1, 0)
txtOut: =Sum(IIf(NameID Is Null, 0, 1)
txtTotalTools: =txtAvailable + txtOut

 

[Stuart McCall]

Nobody ever said it would work in VBA.

I think you are getting mixed up between control source
expressions and VBA code, which use different libraries.

Ah. Yes, you're right. I misunderstood where your code was executing. My
bad. Sorry for bothering you (& almost hijacking the thread).

 

[vandy]

Thanks Marshall,

I was using the QBE grid before . I will take your advise of never switching
it to the design view. When i pasted this query in the SQL view it works
perfect. Thanks a ton for your inputs.

I have a lot to learn and i find this group to be very supportive and helpful.

Thanks once again.

Eg. I

Hey, it's not all bad. At least the out part of the query
is ok ;-)

You lost part of the second On clause. It needs to be:
ON (TrantoolID = ToolID And DateReturned Is Null)

Note that this is a nonequi join and can not be represented
in the QBE grid. Those parenthesis are critical so make
sure they remain as is. Once you get the query
pasted/edited in SQL view, save it and remember to **never**
switch it to design view or the stupid QBE window will mess
it up again.

The three text boxes in the form header or footer section
were intended to display how many tools are available, how
many are out and the total number of tools.
--
Marsh
MVP [MS Access]

I tried your query:

SELECT tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc, IIf(NameID Is
Null,"Available: " & Location,"Out: " & DateTaken & " - " & Name & "(" &
Department & ")") AS Status
FROM tbltoolmaster INNER JOIN (tbltooltransaction LEFT JOIN tblname ON
tbltooltransaction.NameID = tblname.ID) ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID;

Result:
ToolNo ToolDesc Status
ET-2 Heat Gun Out: 5/11/2007 - Andrew Warren(Office)
ET-6 Ferrule Tool Out: 6/11/2007 - Angela Ness(Shop)

yes but when i return the tool back it should say that tool ET-6 returned on
this date status available or in. how to implement that.

Also i did not understand the concept of the 3 text boxes. I have a combo
box which calls up all the employees and the id is the bound column of the
combo and one can select the burrower from the combo. how to make use of the
text boxes. I check for the availablility of the tool and if available i
select the name of the burrower and link the transaction details. Is this
correct.

vandy wrote:
I do realize that i am attempting to complicate my task by not approaching
it in the right direction.

I have a table:

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

You should not store a status because it can be calculated.

I think you can get that kind of list of every tool using:

SELECT Toolno,
ToolDesc,
IIf(NameID Is Null, "Available: " & Location,
"Out: " & DateTaken & " - " &
BorrowerName & "(" & Department &")") As Status,
NameID
FROM tbltoolmaster
LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

On your form/report, make the NameID text box invisible and
use three text box in the form header or footer section:
txtAvailable: =Sum(IIf(NameID Is Null, 1, 0)
txtOut: =Sum(IIf(NameID Is Null, 0, 1)
txtTotalTools: =txtAvailable + txtOut

 

[vandy]

Hi Marshall,

Can you tell me why it does not work if i want to display all the other
details like Modelno, Manufacturer, in the qry. The alert join expression is
not supported. I do understand it is an nonequi join. But if i want
additional details of the tool like Modelno, Manufacturer from tbltoolmaster
how to query it.

It does not allow me to add the two more qry feilds. Can this be done.

SELECT Toolno, ToolDesc,
IIf(NameID Is Null, "Available: " & Location, "Out: " & DateTaken & " - "
& Name & "(" & Department &")") As Status, NameID
FROM tbltoolmaster LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

Right now i have got the desired results but since the toolno and model no
and manufacturer defines the description of the tool it would be very helpful
if i can get those results too.

thanks once again

Hey, it's not all bad. At least the out part of the query
is ok ;-)

You lost part of the second On clause. It needs to be:
ON (TrantoolID = ToolID And DateReturned Is Null)

Note that this is a nonequi join and can not be represented
in the QBE grid. Those parenthesis are critical so make
sure they remain as is. Once you get the query
pasted/edited in SQL view, save it and remember to **never**
switch it to design view or the stupid QBE window will mess
it up again.

The three text boxes in the form header or footer section
were intended to display how many tools are available, how
many are out and the total number of tools.
--
Marsh
MVP [MS Access]

I tried your query:

SELECT tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc, IIf(NameID Is
Null,"Available: " & Location,"Out: " & DateTaken & " - " & Name & "(" &
Department & ")") AS Status
FROM tbltoolmaster INNER JOIN (tbltooltransaction LEFT JOIN tblname ON
tbltooltransaction.NameID = tblname.ID) ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID;

Result:
ToolNo ToolDesc Status
ET-2 Heat Gun Out: 5/11/2007 - Andrew Warren(Office)
ET-6 Ferrule Tool Out: 6/11/2007 - Angela Ness(Shop)

yes but when i return the tool back it should say that tool ET-6 returned on
this date status available or in. how to implement that.

Also i did not understand the concept of the 3 text boxes. I have a combo
box which calls up all the employees and the id is the bound column of the
combo and one can select the burrower from the combo. how to make use of the
text boxes. I check for the availablility of the tool and if available i
select the name of the burrower and link the transaction details. Is this
correct.

vandy wrote:
I do realize that i am attempting to complicate my task by not approaching
it in the right direction.

I have a table:

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

You should not store a status because it can be calculated.

I think you can get that kind of list of every tool using:

SELECT Toolno,
ToolDesc,
IIf(NameID Is Null, "Available: " & Location,
"Out: " & DateTaken & " - " &
BorrowerName & "(" & Department &")") As Status,
NameID
FROM tbltoolmaster
LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

On your form/report, make the NameID text box invisible and
use three text box in the form header or footer section:
txtAvailable: =Sum(IIf(NameID Is Null, 1, 0)
txtOut: =Sum(IIf(NameID Is Null, 0, 1)
txtTotalTools: =txtAvailable + txtOut

 

[vandy]

Hi Marshall,

Sorry , i tried a couple of more time and i got all the feilds i wanted to
be displayed. I must have accidently invoked the qbe. thanks once again and
everything works the way i wanted it.

thanks once again for your inputs , patience and support.

Hi Marshall,

Can you tell me why it does not work if i want to display all the other
details like Modelno, Manufacturer, in the qry. The alert join expression is
not supported. I do understand it is an nonequi join. But if i want
additional details of the tool like Modelno, Manufacturer from tbltoolmaster
how to query it.

It does not allow me to add the two more qry feilds. Can this be done.

SELECT Toolno, ToolDesc,
IIf(NameID Is Null, "Available: " & Location, "Out: " & DateTaken & " - "
& Name & "(" & Department &")") As Status, NameID
FROM tbltoolmaster LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

Right now i have got the desired results but since the toolno and model no
and manufacturer defines the description of the tool it would be very helpful
if i can get those results too.

thanks once again

Hey, it's not all bad. At least the out part of the query
is ok ;-)

You lost part of the second On clause. It needs to be:
ON (TrantoolID = ToolID And DateReturned Is Null)

Note that this is a nonequi join and can not be represented
in the QBE grid. Those parenthesis are critical so make
sure they remain as is. Once you get the query
pasted/edited in SQL view, save it and remember to **never**
switch it to design view or the stupid QBE window will mess
it up again.

The three text boxes in the form header or footer section
were intended to display how many tools are available, how
many are out and the total number of tools.
--
Marsh
MVP [MS Access]

I tried your query:

SELECT tbltoolmaster.ToolNo, tbltoolmaster.ToolDesc, IIf(NameID Is
Null,"Available: " & Location,"Out: " & DateTaken & " - " & Name & "(" &
Department & ")") AS Status
FROM tbltoolmaster INNER JOIN (tbltooltransaction LEFT JOIN tblname ON
tbltooltransaction.NameID = tblname.ID) ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID;

Result:
ToolNo ToolDesc Status
ET-2 Heat Gun Out: 5/11/2007 - Andrew Warren(Office)
ET-6 Ferrule Tool Out: 6/11/2007 - Angela Ness(Shop)

yes but when i return the tool back it should say that tool ET-6 returned on
this date status available or in. how to implement that.

Also i did not understand the concept of the 3 text boxes. I have a combo
box which calls up all the employees and the id is the bound column of the
combo and one can select the burrower from the combo. how to make use of the
text boxes. I check for the availablility of the tool and if available i
select the name of the burrower and link the transaction details. Is this
correct.


vandy wrote:
I do realize that i am attempting to complicate my task by not approaching
it in the right direction.

I have a table:

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.


:
You should not store a status because it can be calculated.

I think you can get that kind of list of every tool using:

SELECT Toolno,
ToolDesc,
IIf(NameID Is Null, "Available: " & Location,
"Out: " & DateTaken & " - " &
BorrowerName & "(" & Department &")") As Status,
NameID
FROM tbltoolmaster
LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

On your form/report, make the NameID text box invisible and
use three text box in the form header or footer section:
txtAvailable: =Sum(IIf(NameID Is Null, 1, 0)
txtOut: =Sum(IIf(NameID Is Null, 0, 1)
txtTotalTools: =txtAvailable + txtOut

 

[Marshall Barton]

Way to go. It's nice to know that it's working now.

FYI, if you should inadvertantly get that query into the
QBE, just close it **without** saving it. Hopefully, you
won't be able to switch it to design view without getting a
warning message.

Once a query is saved from SQL view it should automatically
be in SQL view the next time you open it for editing. To
avoid some of that confusion, I have gone so far as to add a
SQL view button to the query design tool bar.
--
Marsh
MVP [MS Access]

Sorry , i tried a couple of more time and i got all the feilds i wanted to
be displayed. I must have accidently invoked the qbe. thanks once again and
everything works the way i wanted it.

Can you tell me why it does not work if i want to display all the other
details like Modelno, Manufacturer, in the qry. The alert join expression is
not supported. I do understand it is an nonequi join. But if i want
additional details of the tool like Modelno, Manufacturer from tbltoolmaster
how to query it.

It does not allow me to add the two more qry feilds. Can this be done.

SELECT Toolno, ToolDesc,
IIf(NameID Is Null, "Available: " & Location, "Out: " & DateTaken & " - "
& Name & "(" & Department &")") As Status, NameID
FROM tbltoolmaster LEFT JOIN (tbltooltransaction
LEFT JOIN [Borrower Details] ON NameID = ID)
ON (TrantoolID = ToolID And DateReturned Is Null)

Right now i have got the desired results but since the toolno and model no
and manufacturer defines the description of the tool it would be very helpful
if i can get those results too.

You lost part of the second On clause. It needs to be:
ON (TrantoolID = ToolID And DateReturned Is Null)

Note that this is a nonequi join and can not be represented
in the QBE grid. Those parenthesis are critical so make
sure they remain as is. Once you get the query
pasted/edited in SQL view, save it and remember to **never**
switch it to design view or the stupid QBE window will mess
it up again.

The three text boxes in the form header or footer section
were intended to display how many tools are available, how
many are out and the total number of tools.