text box that look up value from another table than the forms record source

[cheekybuddha]

One thing you can try first is:

=DMax("CLng(Nz(antalemnerproduceret, 0))";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

just in case the #Error was caused by Null values for field [antalemnerproduceret]. Again, untested!

 

[zulu100]

OK, glad it works for you.

*** BUT ***
Double check your results - numbers stored as strings sort differently.
E.g Test in Immediate Window (Ctrl+G)

?"1000" > "99"
False

Surely 1000 > 99 so we would expect the result to be True!!!

Perhaps DMax() makes an implicit conversion, but I doubt it (though I haven't tested).

You may need to create a query based on tbl_tidstabel that casts the string as a numeric, and base your DMax() on that query instead.
Eg qry_tidstabel

SELECT [No_], CLng(antalemnerproduceret) AS antalemnerproduceret_nbr FROM tbl_tidstabel;

Then,
=DMax("antalemnerproduceret_nbr";"qry_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

hth,

d

Haven't been able to force an error yet after some testing, so this must OK for now

The above issue is part of a little time clock database that I am doing at work. Its purpose is to keep track of which productionsorders are beeing worked on. Now.. this i work in progress and and feature requests from colleagues keep coming faster than I can implement them
So... I have another issues which might belong i another thread. I don't know. Anyhow here it comes.
Right now I have a continuous form [frm_ctn_List] which record source is a query showing production related stuff (Item no., Description, quality, costumer etc.) Clicking a specific production order no. opens up form [frm_ctn_TidsSkemaPanelfilter]. The production order no. is passed to this form. This form has table [tbl_TidsTabel] as record source. When the employee fill out this form they "Start" the job and select employee from a combo box. The information is saved in the table, and when the form is closed an append query updates a Yes/No field in a status table [frm_ctn_Status] to Yes, indicating that the job has started. Conditional formatting colours a textbox based on this yes/No field. From the form [frm_ctn_TidsSkemaPanelfilter] the employee open form [frm_ctn_Status] from where they can complete/finish the job. This is a Yes/No check box which filter out this specific productionorder from the continuous form [frm_ctn_List].
So to sum up... as of now the employee can start and stop a production order job. When the job is started by any employee a box turn blue indication the job is started. When finished the job disappear from the form [frm_ctn_List]. That's it!
Now I would like make visible (live) which employee are working on which order. An order can be started but that doesn't mean that anybody is working on it. They could have stopped the job and started another one. This isnt visible at the moment.
I cant figure out if I have enough information in my table [tbl_TidsTabel] to accomplish this.
A record is added to this table [tbl_TidsTabel] every time a employee start or stop a job. So start and stop aren't stored i the same record.
Well that became a little longer than intended. Hopes it makes sense.

 

[cheekybuddha]

Haven't been able to force an error yet after some testing, so this must OK for now

Please do a small test for me: Run a query using this SQL:

SELECT
  [No_],
  antalemnerproduceret
FROM tbl_tidstabel
WHERE Len(antalemnerproduceret & '') > 0
ORDER BY
  [No_],
  antalemnerproduceret
;

Post a screenshot of the result here (like your screenshot of tbl_TidsTabel in you previous post)

With regards to your other question, it is probably better asked in new thread. I guess you can use Conditional Formatting.

 

[zulu100]

Please do a small test for me: Run a query using this SQL:

SELECT
  [No_],
  antalemnerproduceret
FROM tbl_tidstabel
WHERE Len(antalemnerproduceret & '') > 0
ORDER BY
  [No_],
  antalemnerproduceret
;

Post a screenshot of the result here (like your screenshot of tbl_TidsTabel in you previous post)

With regards to your other question, it is probably better asked in new thread. I guess you can use Conditional Formatting.

Image attached

 

[cheekybuddha]

Yes, it looks like you haven't encountered incorrect results just through luck!

For instance, with the record where No_ = 570258 and antalemnerproduceret = '800', if you were to change '800' to '80', see if your DMax() formula gives the result you expect.

It would be worth trying to get the revised DMax() expression in post #21 working.

Try it like:

=DMax("CLng(Nz([antalemnerproduceret], 0))";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

 

[zulu100]

I have 3 records with the same production order number. If I change for example 800 to 80, the value on the form change to 700, which is the correct behaviour.

 

[cheekybuddha]

OK, great!

Perhaps it works differently in VBA than when used through a query. I only did a quick test in VBA, not using a query expression.

What happens if you run query (with the new value antalemnerproduceret = '80'):

SELECT
  [No_],
  MAX(antalemnerproduceret) AS Test
FROM tbl_tidstabel
WHERE [No_] = '570258'
GROUP BY
  [No_]
;

If it works and you are happy then mission accomplished!

 

[zulu100]

Like this

 

[cheekybuddha]

Sorry, I needed more coffee!

I was getting confused between which field was text and which field was number!

Of course, antalemnerproduceret is a numeric field in your table (it is [No_] that is the string ), so the DMax/Max sorting will work correctly.

 

[zulu100]

I know what you mean
So know that you are fueled up with coffee maybe you could guide me in a query issue.
I have a reservation table "dbo_KonfAir DRIFT$Reservation Entry" from where I need to filter out.
Entry No_: need to be distinct but is shown in pairs
Source Type "37" and "5406" needs to be in separate columns
The corresponding "Source ID also needs to be in separate columns
I also need the describtion field but the values here are always identical.
See attached image. Top is source - Bottom result
I have something like this but it doesn't seem to work

select
   tabA.Entry_NO,
   tabA.Reservation,
   tabA.[Source Type] as "Source Type_ProductionOrder],
   tabB.[Source Type" as "Source Type_SalesOrder],
   tabA.[Source ID" as "Source ID_ProductionOrder],
   tabB."Source ID" as "Source ID_SalesOrder"
from
   [dbo_KonfAir DRIFTSReservation Entry] tabA,
   [dbo_KonfAir DRIFTSReservation Entry] tabB
where
         tabA.Entry_NO_ = B.Entry_NO_
  and tabA.Entry_NO_ = 33206611
  and tabA."Source Type" = 37
  and tabB."Source Type" = 5406

 

[cheekybuddha]

select
   tabA.Entry_NO,
   tabA.Reservation,
   tabA.[Source Type] as [Source Type_ProductionOrder],
   tabB.[Source Type] as [Source Type_SalesOrder],
   tabA.[Source ID] as [Source ID_ProductionOrder],
   tabB.[Source ID] as [Source ID_SalesOrder"
from
   [dbo_KonfAir DRIFTSReservation Entry] tabA,
   [dbo_KonfAir DRIFTSReservation Entry] tabB
where tabA.Entry_NO_ = B.Entry_NO_
  and tabA.Entry_NO_ = 33206611
  and tabA."Source Type" = 37
  and tabB."Source Type" = 5406

You missed replacing a few double quotes with square brackets.

If tabA.Entry_NO_ is a string datatype then surround 33206611 with single quotes.

 

[zulu100]

I see
Entry_NO is an autonumber
Though Changing to Brackets I still get an error. See attached.
Also I would like to run through all records

 

[cheekybuddha]

Though Changing to Brackets I still get an error. See attached.

Forgot to make the changes in the WHERE clause!

SELECT
   tabA.Entry_NO,
   tabA.Reservation,
   tabA.[Source Type] as [Source Type_ProductionOrder],
   tabB.[Source Type] as [Source Type_SalesOrder],
   tabA.[Source ID] as [Source ID_ProductionOrder],
   tabB.[Source ID] as [Source ID_SalesOrder"
FROM
   [dbo_KonfAir DRIFTSReservation Entry] tabA,
   [dbo_KonfAir DRIFTSReservation Entry] tabB
WHERE tabA.Entry_NO_ = B.Entry_NO_
  and tabA.Entry_NO_ = 33206611
  and tabA.[Source Type] = 37
  and tabB.[Source Type] = 5406
;

Also I would like to run through all records

Please explain what you mean by this

 

[cheekybuddha]

Also, have you got field Entry_NO correct?

Sometimes you have a trailing underscore, other times not.

In the screenshot of the table it contains a space, but not in your SQL

Which is correct???

 

[zulu100]

The sql below give an error. Image attached.
Maybe the sql is wrong build compared to what I would like to accomplish (Try looking at the attached image Query.png)
"Entry No_" come in pairs in this table. The information in the 2 paired records should be populated in to one record for each pair.
The criteria is that "Source Type" is either "37" or "5406"
Hope it makes sense
Now I think I need a coffee refill

SELECT
   tabA.Entry No_,
   tabA.Reservation,
   tabA.[Source Type] as [Source Type_ProductionOrder],
   tabB.[Source Type] as [Source Type_SalesOrder],
   tabA.[Source ID] as [Source ID_ProductionOrder],
   tabB.[Source ID] as [Source ID_SalesOrder]
FROM
   [dbo_KonfAir DRIFTSReservation Entry] tabA,
   [dbo_KonfAir DRIFTSReservation Entry] tabB
WHERE tabA.Entry No_ = B.Entry No_
  and tabA.Entry No_ = 33206611
  and tabA.[Source Type] = 37
  and tabB.[Source Type] = 5406
;

 

[cheekybuddha]

You must use the square brackets around field names with spaces in.

Yes, the SQL as you have it will not give the result you want.

Will the [Entry No_]/[Item No_] combinations always come in pairs?

 

[zulu100]

[Entry No_] always comes in pairs and the corresponding [Item No_] will also be the same 2 values... But a specific value in [Item No_] can be represended multiple times.

 

[cheekybuddha]

It's not clear what determines which of the two records belong to ProductionOrder and which to SalesOrder.

Will [Source Type] = 37 always mean ProductionOrder and [Source Type] = 5406 always mean SalesOrder?

If not, what determines which record is which?

 

[zulu100]

Actually it’s the other way around
37 is the sales order table number and
5404 is the production order table number.
That’s always stay the same.
The reason for this query is to find which production order comes from which sales order.
The only place in our ERP system where they are linked together is in this reservation table.
Hope this makes sense
In the ERP system Microsoft Dynamics NAV we can order track our production orders but when used as linked tables in Access I have to make the connection in this query

 

[zulu100]

In the illustration image I made 570260 is the production order number coming from sales order number 90394