Query returning some data, some times

[bridgeo730]

Hi. I have two queries from which I am making a third query. However, in this third query, only some of the data is appearing. I work with clients with university degrees, and I want the third query to say the school, the year, the level, and the major. However, for some strange reason the major in the final query is not appearing at all times. This is strange, since it appears in the two original queries. I want to say it has something to do with the code, but sometimes it pulls the major, sometimes it doesn't (copying code here, and then you can see from the photo that some of the majors appear while the others don't, for no rhyme or reason). When I got back to the original queries from which this third one is pulled, everything is displayed as I wish, though on the original queries I don't have this code:



Degree: [Degree 1 Campus] & " " & [Degree 1 Year] & " " & [Degree 1 Type] & " " & [Degree 1 Major]+Chr$(13)+Chr$(10)+[Degree 2 Campus] & " " & [Degree 2 Year] & " " & [Degree 2 Type] & " " & [Degree 2 Major]+Chr$(13)+Chr$(10)+[Degree 3 Campus] & " " & [Degree 3 Year] & " " & [Degree 3 Type] & " " & [Degree 3 Major]

[ranman256]

if 1 field is null, all fields in the calculation will be null.
fix with NZ,

NZ([field],"")

[bridgeo730]

These fields are not null, though. For example, where it says "Illinois 1972 BS" and then nothing, the data is supposed to say "Illinois 1972 BS Social Studies". It's just not appearing, but as you can see, Nuclear Engineering appears, and so does Physics and Marketing, and it's all from the same code.

[ssanfu]

First suggestion would be to remove ALL SPACES in object names.


Instead of " +Chr$(13)+Chr$(10)+ ", use " & vbNewLine & " (or " & vbCrLf & ")
Query column would look like:

Degree: [Degree 1 Campus] & " " & [Degree 1 Year] & " "  & [Degree 1 Type] & " " & [Degree 1  Major] & vbNewLine & [Degree 2 Campus] & " " & [Degree 2  Year] & " " & [Degree 2 Type] & " " & [Degree 2  Major] & vbNewLine & [Degree 3 Campus] & " " & [Degree 3  Year] & " " & [Degree 3 Type] & " " & [Degree 3 Major]

In a new column, try this (for troubleshooting):

DegreeTest: NZ([Degree 1 Campus],"Oops") & " " & NZ([Degree 1 Year],"Oops") & " " & NZ([Degree 1 Type],"Oops") & " " & NZ([Degree 1 Major],"Oops") & vbNewLine & NZ([Degree 2 Campus],"Oops") & " " & NZ([Degree 2 Year],"Oops") & " " & NZ([Degree 2 Type],"Oops") & " " & NZ([Degree 2 Major],"Oops") & vbNewLine & NZ([Degree 3 Campus],"Oops") & " " & NZ([Degree 3 Year],"Oops") & " " & NZ([Degree 3 Type],"Oops") & " " & NZ([Degree 3 Major],"Oops")

Or you could add 3 columns to see if you can find a problem:

Degree1: [Degree 1 Campus] & " " & [Degree 1 Year] & " " & [Degree 1 Type] & " " & [Degree 1 Major]
Degree2: [Degree 2 Campus] & " " & [Degree 2 Year] & " " & [Degree 2 Type] & " " & [Degree 2 Major]
Degree3: [Degree 3 Campus] & " " & [Degree 3 Year] & " " & [Degree 3 Type] & " " & [Degree 3 Major]

You could post a picture of the relationship window.



You could post your dB for analysis. Only need ~20 records for testing.
Change any sensitive data. Do a "Compact and Repair", then zip it.

[bridgeo730]

First suggestion would be to remove ALL SPACES in object names.


Instead of " +Chr$(13)+Chr$(10)+ ", use " & vbNewLine & " (or " & vbCrLf & ")
Query column would look like:

Degree: [Degree 1 Campus] & " " & [Degree 1 Year] & " "  & [Degree 1 Type] & " " & [Degree 1  Major] & vbNewLine & [Degree 2 Campus] & " " & [Degree 2  Year] & " " & [Degree 2 Type] & " " & [Degree 2  Major] & vbNewLine & [Degree 3 Campus] & " " & [Degree 3  Year] & " " & [Degree 3 Type] & " " & [Degree 3 Major]

In a new column, try this (for troubleshooting):

DegreeTest: NZ([Degree 1 Campus],"Oops") & " " & NZ([Degree 1 Year],"Oops") & " " & NZ([Degree 1 Type],"Oops") & " " & NZ([Degree 1 Major],"Oops") & vbNewLine & NZ([Degree 2 Campus],"Oops") & " " & NZ([Degree 2 Year],"Oops") & " " & NZ([Degree 2 Type],"Oops") & " " & NZ([Degree 2 Major],"Oops") & vbNewLine & NZ([Degree 3 Campus],"Oops") & " " & NZ([Degree 3 Year],"Oops") & " " & NZ([Degree 3 Type],"Oops") & " " & NZ([Degree 3 Major],"Oops")

Or you could add 3 columns to see if you can find a problem:

Degree1: [Degree 1 Campus] & " " & [Degree 1 Year] & " " & [Degree 1 Type] & " " & [Degree 1 Major]
Degree2: [Degree 2 Campus] & " " & [Degree 2 Year] & " " & [Degree 2 Type] & " " & [Degree 2 Major]
Degree3: [Degree 3 Campus] & " " & [Degree 3 Year] & " " & [Degree 3 Type] & " " & [Degree 3 Major]

You could post a picture of the relationship window.


You could post your dB for analysis. Only need ~20 records for testing.
Change any sensitive data. Do a "Compact and Repair", then zip it.

I did try the vbNewLine and also the vbCrLf exactly how you have it, copied and pasted it, but keep getting an error that I need a parameter value for those. So I couldn't test suggestions 1 or 2. THOUGH, even when I enter "x" as a parameter value (I have no idea what parameter it wants) it is now seeing the major, which is a good sign. When I try separating them out into three columns, I get the same issue with the major missing on some names, others it appears. It seems random. I'm uploading a picture of the relationships here.

[bridgeo730]

Actually! I was able to just now try option two, leaving my +Chr$(13)+Chr$(10)I added NZ to each field (I left out the Oops, not sure what that was for). And it works now! In the end, this is the string:
Degree: Nz([Degree 1 Campus]) & " " & Nz([Degree 1 Year]) & " " & Nz([Degree 1 Type]) & " " & Nz([Degree 1 Major])+Chr$(13)+Chr$(10)+Nz([Degree 2 Campus]) & " " & Nz([Degree 2 Year]) & " " & Nz([Degree 2 Type]) & " " & Nz([Degree 2 Major])+Chr$(13)+Chr$(10)+Nz([Degree 3 Campus]) & " " & Nz([Degree 3 Year]) & " " & Nz([Degree 3 Type]) & " " & Nz([Degree 3 Major])

Still hard to tell what the error was and why, but it's working now! Thanks for the suggestions!

[ssanfu]

Open up the IDE and check your references.
Do you have reference for "Microsoft Office XX.0 Object Library"?
With Office 2010, I have "Microsoft Office 14.0 Object Library".
Add the reference, then try vbNewLine or vbCrLf.
----------------------------------

added NZ to each field (I left out the Oops, not sure what that was for)

It was for troubleshooting. If you see "Oops", you would know that the record had a NULL value.

The syntax of the NZ function is
Nz(Value, ValueIfNull)

This means if the VALUE is NULL, return ValueIfNull. So if you have "Nz([Degree1Type], "Oops"), and "Degree1Type" is NULL, you would see "Oops". Much easier to see rather than an empty string!

For strings, I use Nz([Degree1Type],"")
For numbers, I would use Nz(Amount], 0) or Nz(Amount], -1111) Whatever number I want returned if the field is NULL.

See Access Help for NZ() details.
----------------------------------


If you are going to use "+Chr$(13)+Chr$(10)+", change the pluses to ampersands : "& Chr(13) & Chr(10) &".



----------------------------------
BTW, These variable types have been depreciated. They are there for backward compatibility, but MS could remove them at any time.

$	String
%	Integer
&	Long
!	Single
#	Double
##	_FLOAT

Instead of "Chr$(13)+Chr$(10)", use "Chr(13) & Chr(10) "