Creating new fields programmatically

  • Thread starter Thread starter Squiggles
  • Start date Start date
S

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
 
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 <--
 
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.
 
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 <--
 
hi,

Stefan said:
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 <--
 
hi,

Stefan said:
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
 
Back
Top