Hide #Deleted from query

[YouMust]

i have a query
https://ibb.co/7Vz4ByT
https://ibb.co/sm4L11s
When the 'text' field is empty I still get that line in the query as desired, but it places #deleted into that field.

So when my form pulls that query it displays the #deleted, I'd like that to be left blank, is that possible?

https://ibb.co/XWZ48q2


Lastly, I cant make that 'text' box auto grow. Ideally, constrain the width but allow to grow vertically? increasing the total height of the row

Thanks for any help in advance

 

[theDBguy]

Hi. For some reason, I don't see any images in your post (if you had any).

 

[YouMust]

not sure why the tags didnt work, I've just put the url's in now so atleast they are clickable now. thanks for letting me know!

 

[theDBguy]

Hi. If you open the BOM_NOTES table and look in the TEXT field, do you see any records with "#Deleted" in them? Just curious...

 

[YouMust]

no, the table is populated
the qry shows #deleted when there is no data from that table (couldn't find parent/seq)

note the arrows on the query design view

basically i still want the query to return whatever data it has even if there is no data in BOM_NOTES table.

 

[theDBguy]

Hi. Thanks. I just had to make sure because you would normally see a Null value instead of #Deleted, unless you have something in the Format property of the query column or table field. Do you? If so, what is it?

 

[YouMust]

thanks @theDBguy
There is no formatting applied to that field.
the query is here: https://ibb.co/7Vz4ByT

 

[theDBguy]

Okay. If this is an Access database, are you able to post a sample copy with test data? Just looking at your pictures may not be enough (at least for me).

 

[Gasman]

Add the rest of the fields from BOM_Notes. What do they show?

Isn't it telling you that the child records have been deleted?

 

[YouMust]

the tables are not in an access db. its PSQL. the tables are linked in access and used by my form.

it only shows #deleted if there is no data to display from BOM_NOTES. it cant actually delete it as there is no data to delete, it's null.

not sure how i could upload this

 

[theDBguy]

the tables are not in an access db. its PSQL. the tables are linked in access and used by my form.

it only shows #deleted if there is no data to display from BOM_NOTES. it cant actually delete it as there is no data to delete, it's null.

not sure how i could upload this

Hi. Not sure how to help you then. Take a look at the small demo I created to show what I was thinking. If you open Table1, you'll see some empty fields. And when you open Query1, you'll see #Deleted in those empty cells. Not sure if this is the case for you though.

 

[Gasman]

Hi. Not sure how to help you then. Take a look at the small demo I created to show what I was thinking. If you open Table1, you'll see some empty fields. And when you open Query1, you'll see #Deleted in those empty cells. Not sure if this is the case for you though.

@theDBguy,

How did you do that?

 

[YouMust]

I'm sry DBguy, if i could upload it i would. maybe the linked table/s is the issue?
I'll try to figure a way of uploading it

but yes, that demo is doing the same thing isn't it!

 

[theDBguy]

@theDBguy,

How did you do that?

Hi Gasman. Check out the Format property.

 

[theDBguy]

I'm sry DBguy, if i could upload it i would. maybe the linked table/s is the issue?
I'll try to figure a way of uploading it

but yes, that demo is doing the same thing isn't it!

Hi YouMust. The same goes for you. If the Format property is empty, then I'm not sure what else could be causing it. Maybe we can do a screenshare or something.

 

[YouMust]

a screen share would be great, though Im off to my nieces birthday dinner very shortly.

maybe we could pick this up tomorrow?

I'm genuinely thankful for your help!

 

[theDBguy]

a screen share would be great, though Im off to my nieces birthday dinner very shortly.

maybe we could pick this up tomorrow?

I'm genuinely thankful for your help!

Oh, sounds like you're on a different time zone as me. I'm on West Coast (Pacific) time zone, just FYI.

 

[YouMust]

ah, GMT here.

 

[theDBguy]

ah, GMT here.

Okay, just keep that in mind, but I think it should still work. We could meet as I'm starting the day, right before you end it. Cheers!

 

[YouMust]

hey @theDBguy

any chance we could pick this up?

really thankful for your help, ive just had a number of issues come up, i've finally got some free time.