Creating new fields programmatically

[Squiggles]

Hi,

I'm somewhat new to MS Access but not to programming. I'm hoping
someone can point me in the right direction.

Here's my quest. I'd like to create a field (FldC) based on the
following logic:

If FldA = "-" Then
FldC = FldB
Else
FldC = FldA
End If

And to complicate matters, I'd like to bump* a Group total by 1 every
time FldC is "Empty" (Null or "").

I'm currently considering this logic at the "Report" level but it
might be handy at the "Query" level also, selecting or excluding
records based on the derived value of "FldC".

A direct answer would be neat but a pointer to some MS Access tome**
would be just as helpful to me.

Thanks in advance

*Or set another new field to 0 or 1 if we're working at the Query
level
**chapter and verse please

 

[Stefan Hoffmann]

hi,

Here's my quest. I'd like to create a field (FldC) based on the
following logic:

If FldA = "-" Then
FldC = FldB
Else
FldC = FldA
End If

Create a new query based on your table. Add the following as field
expression:

FldC: Iif(Nz([FldA], "") = "-", [FldB], [FldB])

And to complicate matters, I'd like to bump* a Group total by 1 every
time FldC is "Empty" (Null or "").

This makes no sense. Over what expression (fields) do you want to create
the group?


mfG
--> stefan <--

 

[Squiggles]

hi,

Here's my quest. I'd like to create a field (FldC) based on the
following logic:

If FldA = "-" Then
  FldC = FldB
Else
  FldC = FldA
End If

Create a new query based on your table. Add the following as field
expression:

FldC: Iif(Nz([FldA], "") = "-", [FldB], [FldB])

And to complicate matters, I'd like to bump* a Group total by 1 every
time FldC is "Empty" (Null or "").

This makes no sense. Over what expression (fields) do you want to create
the group?

mfG
--> stefan <--

I'm sorting and grouping records based on street name and address.
Every time the street name changes I'd like to know how many records
had FldC "Empty" on that street.

 

[Stefan Hoffmann]

hi,

I'm sorting and grouping records based on street name and address.
Every time the street name changes I'd like to know how many records
had FldC "Empty" on that street.

This basically another query:

SELECT Count(*), [StreetName]
FROM [yourTable]
WHERE Len(Trim(Nz([FldC], ""))) = 0
GROUP BY [StreetName]


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I'm sorting and grouping records based on street name and address.
Every time the street name changes I'd like to know how many records
had FldC "Empty" on that street.

This basically another query:

SELECT Count(*), [StreetName]
FROM [yourTable]
WHERE Len(Trim(Nz([FldC], ""))) = 0
GROUP BY [StreetName]

*#@, thats not correct, should be:

SELECT O.[StreetName],
(
SELECT Count(*)
FROM [yourTable] I
WHERE I.[StreetName] = O.[StreetName]
AND Len(Trim(Nz(Iif(Nz(I.[FldA], "") = "-",
I.[FldB], I.[FldA]), ""))) = 0
) AS [Count]
FROM [yourTable] O

and you need to a report with an group on [StreetName] where you display
this count.


mfG
--> stefan <--

 

[Squiggles]

hi,

This basically another query:

SELECT Count(*), [StreetName]
FROM [yourTable]
WHERE Len(Trim(Nz([FldC], ""))) = 0
GROUP BY [StreetName]

*#@, thats not correct, should be:

SELECT O.[StreetName],
        (
           SELECT Count(*)
           FROM [yourTable] I
           WHERE I.[StreetName] = O.[StreetName]
           AND Len(Trim(Nz(Iif(Nz(I.[FldA], "") = "-",
                               I.[FldB], I.[FldA]), ""))) = 0
        ) AS [Count]
FROM [yourTable] O

and you need to a report with an group on [StreetName] where you display
this count.

mfG
--> stefan <--

Ok - great. Thank you